Excel Tip - Changing Case in Excel

When importing or copying data into an Excel spreadsheet, you may find on occasions that the text is displayed in the wrong case i.e. UPPERCASE.

Within Word, it is quite straight forward to change the case automatically (with the word highlighted press Shift + F3) however, in Excel there is a little more to it.

Here’s how you can write a formula to automatically change the case rather than painstakingly retyping all the data manually…

You will see in the picture below that everyone’s last name in column B is displayed in uppercase however, we would prefer that the names are displayed in Title Case (i.e. the first letter is in uppercase and the remaining letters in lowercase) – here’s what to do:
 Name List
Using our example as shown above…

1. In a blank cell next to the first row (D3 in our example) type in the formula: =Proper(B3) and then press Enter on the keyboard. You will see that the results of the formula has displayed Cathy’s last name in title case.
2. Copy the formula down to D9 to replicate this process for all other last names.

With all the last names in the correct case within column D, the next step is to copy them from column D and overwrite what is in column B:

1. Highlight D3 to D9
2. Click Copy
3. Click in cell B3
4. Right Click on the highlighted section, Click Paste Special, Paste Values

 Paste Values
Now that we have the last names displayed properly in column B, you can go ahead and delete the formulas from within cells D3 to D9.

NOTE: In the above example we have used =Proper() to change text into Title Case. In addition to this you can use =Upper() to change text into UPPERCASE or =Lower() to lowercase.

Pin it