How To Clean Data In Excel -- Make Data Entries More Uniform

A way to make data entries more uniform is by using the formula LOWER. LOWER turns all letters of a specific cell into lowercase letters. This is a quick way of making sure all data entries for a category are bucketed together. A common case where this formula comes in handy is with place names, as these tend to be spelled in various ways, e.g. Boston, boston, bOSTN, or BOSTON. With this formula, all the data entries will be spelled boston. 

Excel formula:

(the ‘text’ part in the parentheses refers to the cell you want to convert to lowercase letters)

A variation of this formula is UPPER, which works in the same fashion as LOWER but turns all letters into uppercase letters. 

As you might have noticed, place names will not necessarily be spelled correctly if you only use one of these formulas, but if you combine these two formulas and the LEFT and MIDDLE formulas, you can makes sure it’s spelled Boston instead of boston or BOSTON.

This is slightly more complicated, so hang tight. Regardless of if Boston is spelled, BOSTON, boston, BostoN, or bOSTON, you would want the first letter to be capitalized (UPPER) and the remaining characters to be lowercase (LOWER). To do this you will have to combine the formulas and functions UPPER, LEFT, '&', UPPER, and MID.

Let's break it down to see what needs to be done. 

  • #1. Capture the first character 'b' 
  • #2. Make sure the first character is always capitalized
  • #3. Capture everything but the first character i.e. 'oston'
  • #4. Make sure everything after the first character is lowercase. 
  • #5. Combine 'B' with 'oston'

For the purpose of simplicity, let's assume the data entry (bOSTON) is in cell A2, but this could obviously be any cell. 

#1 and #2: To capture the first letter we use the LEFT formula, but since this needs to be capitalized we wrap the UPPER formula around it, so it looks like this:

=UPPER(LEFT(A2,1)    -> This will return 'B'

#3 and #4: To capture everything but the first character we use the MID formula, and to make sure it's all lowercase, we wrap it in the LOWER formula. It should look like this:

=LOWER(MID(A2,2,60) -> This will return 'oston'

Notice the last part of the formula that says 60 , we could have limited that to 5, which is the number of characters 'oston' amounts to, but if you have a column with a lot of different city names, make sure you capture all characters by having a large number in the latter part of the formula. 

#5: To combine all four steps we can use the ampersand trick we learned above. This means the final formula should look like this:

(Notice you only include equal signs once at the beginning when you combine formulas)
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request