Excel 2010: Consistent Case Formatting

“In Excel, is there an easy way to change text with different case formatting to all have consistent case formatting – such as all upper case, lower case or have the first letter in each word capitalized?”

aai-excel-case

Issue: An Excel spreadsheet containing a list of registrant names that had been entered inconsistently – some were all lower case, others were all upper case and some were a mix. The client was not looking forward to having to correct each of these manually. A time-saving solution to the tedious task was required.

Solution: Excel can actually make this quick, easy and (relatively) painless. The program comes with built-in functions to change the case to UPPER, LOWER, or PROPER. “PROPER” case has the first letter in each word capitalized.

Step-by-Step Instructions: The following step-by-step solutions show how to use this function to convert data to the “PROPER” case.

1. Once you have the names in your spreadsheet, insert a column after the registrant column. To do this, simply right click on the column to the RIGHT of where you plan on inserting the new column and select Insert. In the example below, I need to insert a column between Registrant Name and Phone, so I will need to right click on my Phone column.

Note: If the registrant name column is divided into two columns (i.e. first and last name), you will need to insert two columns: one in between first and last name and then one after last name.

aai-excel-case01

2. In the new column, select the cell to the right of  the first name you wish to change and enter the formula =PROPER(cell reference).

In my example, the first name I wish to change is Jane Smith. The cell reference for her name is A2. My formula, therefore, will be “=PROPER(A2)” and it will be entered in cell B2.

aai-excel-case02

3. Copy that formula down.

To copy the formula down, drag the small black square in the lower right-hand corner, double-click it, or, my personal favourite, hold down the SHIFT key while pressing the down arrow to select all the cells that I want my formula to be entered in. Then press CTRL and D.

Note: If your data is in a table, the data will automatically populate in the cells you require after entering it.

aai-excel-case03

4. Copy the highlighted cells: Press CTRL and C or right click and select Copy. Then, in cell A2, right click, select Paste and then select Values.

aai-excel-case04

Alternatively, you can click the dropdown arrow under Paste in the Clipboard group (Home tab) and then select Paste Values. This will allow you to only copy the names and not the underlying formulas. If you try to copy the formulas, you will get an invalid cell reference error; remember, your original formula referenced cell A2 which you are now trying to paste into.  Then, remove column B by right clicking and selecting Delete.

aai-excel-case05
5. The following will be the final result:

aai-excel-case06

If you need to, you can interchange the PROPER function with LOWER or UPPER; all of the above steps will remain the same.

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s