Uppercase to Proper Case in Excel

Suppose you’ve been supplied a Microsoft Excel spreadsheet with the company names, addresses, and other data for a particular industry (perhaps for a mail merge). The spreadsheet author typed all the company names in uppercase letters, but you’d rather have them in proper case. That is you’d like to turn every instance of company names like “ACME, INC.” to “Acme, Inc.” This will look much better on envelopes or labels.

There are two easy ways to change. First, you can copy and paste the column of uppercase entries into a Word document. Then select “Format/Change Case”… and select “Title Case” from the list of options…and click “OK.” Now just copy and paste that column of text back into the Excel document.

Alternatively, you don’t need to leave the Excel workbook and open Word at all. Create a new column next to the uppercase column. Make sure the Formula Toolbar is open (“View/Formula Toolbar”). Place the cursor (white cross) into the first cell adjacent to the first cell of uppercase text. Then click the Equal symbol in the Formula Toolbar (“=”). This inserts an equal sign into the formula field. Click on the pull down menu at the very left (it holds all the Functions Excel has available.) If you don’t see the word “PROPER” in the pull down menu, select more “More Functions…” and this brings up a dialog box. Click on “Text” in the “Function Category” field, then click “PROPER” in the “Function Name” field. This brings up a another dialog box. Beside the “Text” entry field is an up arrow. Click on that, then select the first cell with the uppercase text you’d like to change. This will insert the cell address into the function, such that (for example) the formula bar will read “=PROPER(A1)”. Hit the return key, and the function is now applied to the cell. You will see in the new cell the proper case version of the uppercase entry.

Now hover the white cross over the bottom right corner of the new cell until it changes to a thinner, black cross. Then click and drag the cursor straight down to select the same number of cells that there are in the uppercase column. This will apply the formula to each successive cell. You will see all the text in proper case.

You’re not quite done. The column with the proper case text is really just a column of the results of the formula, not real text (you can tell if you try to edit the text in these cells.) Simply select all the new cells and hit “Copy”. Then select Edit/Paste Special, and click on the “Values” button under the “Paste” option list. Now you can edit the text in this column.

Finally, delete the original uppercase text column.

See ya soon.

P.S. For these videos, the first uses a soundtrack from William Lamy at AudioBank.fm. The second video is music I composed with the help of Garageband. You can also view the first video and the second video on Youtube.


About this entry