Cleaning Up Messy Excel Data, Part 3: UPPER, LOWER, and PROPER Functions (with examples)
Margaret Efron
Business Systems Analyst | Master's in Business Analytics Candidate, William & Mary
Welcome to part 3 of my series on cleaning up messy Excel data.
Today I'll give specific examples of how to use the UPPER, LOWER, and PROPER functions.
Use the UPPER, LOWER, or PROPER functions: These functions can be used to convert text to uppercase, lowercase, or proper case (capitalizing the first letter of each word). For example, if your data is in cell A1, you can use =UPPER(A1), =LOWER(A1), or =PROPER(A1) to change the case of the text.
The most common use case is to use the PROPER function to correct user-entered fields for first name, last name, and/or city and state. Instead of “mary JONES,” you can correct this to “Mary Jones.” Instead of “virGinia,” you can correct this to “Virginia.”
In my opinion, the UPPER and LOWER functions are not as commonly used, but I will list examples below.
The UPPER function converts your text to uppercase.
The LOWER function converts your text to lowercase. Again, this is not very frequently used at my work, but I will give an example below.
These are just a few examples of the functions and techniques you can use to clean up messy Excel data. The specific functions and methods you choose will depend on the nature of your data and the cleaning requirements. These techniques are particularly helpful when correcting user-entered errors, such as data in surveys like Qualtrics.