Tag Archives: Len

XL FORMULA FOCUS: PROPER, UPPER, LOWER, LEN (your first four text functions)

XLCalibre Basic Excel Text Functions

The above picture gives a very quick lesson in four different functions. Here’s what they do:

PROPER

Remember proper nouns from English class? Names of things, you know, stuff that deserves to start with a capital letter. This gives those words what they deserve. It’s particularly useful if you have a long list of names or address that have been entered in different ways, with all capitals, no capitals, or just plain shoddy capitalisation. This makes the first letter of each word a capital, and the rest lower case (of course it doesn’t work on words like XLCalibre (or McDonalds) where you need more than one capital).

UPPER

This makes all letters in the specified text upper case. It’s that simple.

[showmyads]

LOWER

Guess what this does – it makes it all lower case.

LEN

Just to throw you off guard, this tells you how many characters the text contains (LEN stands for length). XLCalibre is 9 characters long. Why is this useful? Well, the truth is on its not always that useful, but when combined with other functions it can be mighty handy. Trust me, if you’re going to learn more Excel functions you will want this in your repertoire.

So there you have it, an introduction to four text functions within the space of 5 minutes.

Splitting names

Today on LinkedIn somebody posed the following question (for anonymity I have changed any names given):

Sorting by Last Name

I have a list of 2,000 names by First, Middle, Last, Suffix (Jr or I,II, or III) I need to be able to Sort by last name. I know how to use “Text to Column” but unfortunately there is not a consistancy in the names, I have some that have just First and Last name, I have some that may have a space in their last name such as “De Souza” or “John Dave Jones Jr II” any suggestions.

The issue wasn’t really about sorting, which would be easy with autofilters (CTRL+Shift+L to apply autofilters). The problem was separating out the parts of the names, and lining them up in the right columns. Usually text to columns would fix this, but doing it with the different formats mentioned in the post would have left last names in various different columns.

I find this is a common predicament in HR, where lists of names pulled from various sources are full of inconsistencies, and therefore hard to manipulate or ‘plug in’ to other systems or spreadsheets.

My solution was not particularly elegant. You are rarely afforded the time to create the perfect single formula answer that covers all angles. But it does show you some text-manipulation functions at work.

I e-mailed the file to the poster. They mentioned 5 exceptions that it had not worked for, and they all included initials (I hadn’t thought of this possibility). This leads to my next point: testing. If at all possible you should use your spreadsheets from beginning to end with real data to check if they’re working before they go to your customer. Even better, get someone else to do this for you, as its easy to miss something when you’re buried in details.

Here you can access the spreadsheet that I created. See if you can understand how it works. How would you do it differently? Can you adjust it to work for with initials? Feel free to send your spreadsheets in to admin@xlcalibre.com.

If you’d be interested in similar discussions, come join the brand new XLCalibre Group on LinkedIn.