Discover how functions can be utilized to control the case of characters, specifically when combining content from different columns into a single one. Learn how to manipulate the UPPER, LOWER, and PROPER functions to enhance legibility within data, particularly when dealing with a mixture of numbers and letters.
Key Insights
- The UPPER, LOWER, and PROPER functions can be utilized to control the case of characters when combining different columns into one.
- The UPPER function is used to transform all alpha characters to uppercase.
- To alter the case in a specific field only, the existing formula can be nested within the UPPER function.
- The LOWER function, on the other hand, converts all alpha characters to lowercase.
- The PROPER function converts the first character to uppercase and the rest to lowercase, also known as Title case.
- These case-changing functions are crucial for enhancing legibility within data, especially when combining numbers and letters.
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.
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.