How To…Change cells with text in all CAPS in Microsoft Excel

Recently, one of my clients expressed her frustration with cleaning up lists in Excel. They export various contact lists from their CRM and much of that data was originally entered with the customer name all in capital letters. She hates the way that looks on their mailing labels, so their export goes through Excel to clean it up before the mail merge with Word.

The good news is that changing cells in Excel from all caps to normal upper and lower case text is a simple formula. Here’s how it works:

1. Insert a column next to the column of text you want to change. We’ll say the column in all caps is column A. Inserting a column to the left of column A will move all the data in column A to column B, and column A is now blank.

2. Enter the formula in column A on the first row that holds data, which we’ll say is row 1. So, in cell A1 you enter the following:

=PROPER(B1)

3. The result of the formula is the all caps text in B1 is now in normal upper and lower case text in A1. Then copy the formula all the way to the bottom of the data. Now you have two columns with the same information. However, column A is a formula, so if you delete column B, the formula no longer works. Here is the quick fix.

4. Select column A, all of it, by simply clicking on the letter A at the top of the column. Click Edit, Copy – or in Excel07, it is Home, Copy.

5. Leave column A highlighted and click Edit, Paste Special, then select Paste Values, OK – or in Excel07, it is Home, the word Paste to open the pull-down menu, and then Paste Values. Now all the formulas are simply text.

6. Press the ESC key to remove the selection around column A. Now you can delete column B. Select column B by clicking on the letter B at the top of the column, then Edit, Delete – or in Excel07, Home, Delete.

Example of the results of these steps:

ELLEN in cell A1 – then insert a column
ELLEN in cell B1 – column A is blank
=PROPER(B1) in cell A1 – the cell will show the text Ellen
copy formula down the column
copy column A and paste values into column A
delete column B
Ellen still in cell A1

As with many step-by-step written instructions, it looks like more work than it really is. Most of these steps are simple clicks that take only a second.

Give it a try and let me know how it works for you.

Ellen DePasquale – The Software Revitalist™