Skip to main content
/3 min read

Using Functions to Control Case

Case Functions

UPPER()

Converts to ALL CAPS. UPPER("hello") returns "HELLO".

LOWER()

Converts to all lowercase. Useful before comparing strings.

PROPER()

Capitalizes the first letter of each word — title case for names.

Combine with TRIM

TRIM(PROPER(A1)) cleans whitespace and fixes case in one step.

Master Excel at Noble Desktop

Noble Desktop's Excel Bootcamp covers formulas, pivot tables, data analysis, and VBA.

Learn how to use Functions to control Case

Using Functions to Control Case

In another video, we combined content from two different columns – Last Name and Social Security Number – into a single column, using the LEFT and RIGHT functions, creating an Employee ID value. You can see the values in column E, and when I click in one of the cells and we look at the formula bar, we see the two functions, combined using the ampersand character.

It worked well, but what I don’t love about the results is that the alpha characters are in the same case as they were typed in the source column, column B, and I want them to be in ALL CAPS in column E. I don’t want the Last Name column data to be in all caps, I like it to remain in Title, or Proper case, so I need to deal with the case in the Employee ID field only.

How do we do that? We wrap the existing formula – the set of combined functions – in the UPPER function.

So, I’ll select the contents of cell E4 and edit it as follows:

I start by inserting the word UPPER between the equal sign and the beginning of the LEFT function, and add an opening parenthesis.

Then, to supply the closing parenthesis for the UPPER function, inside of which the LEFT and RIGHT functions are now nested, I click at the end and type the closing parenthesis.

When I press ENTER, the alpha characters are all in CAPS.

I can now drag this down the column with the Fill handle, to repeat it for everyone in the company, giving them all a much more legible Employee ID.

Other case-controlling functions include:

LOWER, which does the opposite of UPPER – it converts all alpha characters to lower case, as shown here in this quick demonstration:

And PROPER, which puts the alpha characters in Title case, with the first character in CAPS and the rest in lower case, as shown here – done on both text that was originally in all lower case and then again on text that was in all CAPS.

Case-changing functions offer you nearly endless potential for greater legibility within your data, especially when working with a combination of numbers and letters.