Even though numbers are at the heart of any data analysis, remember that a string of text is data, too.
The good news, though, is that Microsoft Excel includes several functions to automate text manipulation (or, in the case of CONCAT, helps combine two or more separate cells of text).
Using UPPER, LOWER, PROPER
Excel doesn't have a Change Case button for changing capitalization. However, you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
In the example below, the PROPER function is used to convert the uppercase names in column A to proper case, which capitalizes only the first letter in each name.
Here's what to do:
- First, locate a column that will contain the text you want to convert. In this example, there's a new column (B) added to the right of the Customer Name column
- In cell B2, type =PROPER(A2), then press Enter.
- This formula converts the name in cell A2 from uppercase to proper case. To convert the text to lowercase, type =LOWER(A2) instead. Use =UPPER(A2) to convert... you get the idea.
You can now use AutoFill to complete the remaining cells in the range. The quickest way to do this is by selecting cell B2, and then double-clicking the small black square (the AutoFill handle) that appears in the lower-right corner of the cell.
A full breakdown is covered in the video below.
Now - go out there and live your best life when it comes to Excel text!