How To Use Excel To Change Word From ALL CAP to Lowercase or PROPER

Most clients ask us how we  clean up columns, especially changing lowercase words to all capitals, and changing phone numbers so they don't have spaces and dashes. If you use Excel a lot, you will know these formulas or tricks.  This post is for those who do not have to do this very often, and need some help.

1. Using Excel to change Words from All CAP to All lowercase, or to PROPER

• you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
• First, insert a temporary column next to the column that contains the text you want to convert
• In the temporary column, for example,  cell B2, type =PROPER(A2)
• The other choices are  =UPPER(A2) or  =LOWER(A2)

Now, all of the names, countries, states, provinces, etc. can have standardized type.

2. Removing spaces and symbols in a phone number

• Often we get a list of telephone numbers that look like this

                              

• To get rid of the (, ), –, Ext. 0000, use the find and replace function
• To get rid of the space, use the find and replace, using the space bar
  1. Press Ctrl + Space to select all cells in a column.
  2. Press Ctrl + H to open the “Find & Replace” dialog box.
  3. Press Space bar in the Find What field and make sure the “Replace with” field is empty.

 

Posted on March 1 2017 by Judith Hirst