Use the Excel case functions to change the case of text in Excel -- PROPER, LOWER and UPPER. These functions change the case of letters in a text string, and have no effect on other characters.
When people enter their contact information in online forms, the data you collect might be messy. This video has text in upper, lower, proper and mixed case. Use the PROPER function to clean up the first and last names, and make them consistent.
If you'd like to read the PROPER function video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript
Here is the full transcript for the PROPER function video.
Let's take a look at an easy way to fix a common issue. Here you can see that some of the entries are all lower case, some were caps. Let's fix it really easily.
This example shows new subscriptions for your newsletter, but it could be data from any kind of form entry -- attendees, recent orders, you name it. Any time that humans are entering their names, the chances are that the report you get will not be uniform.
This can be a pain, if you need to use that data. Let's fix it really easily
Make a Table
I'm going to make this a table, because it will make it much easier to copy down the formula. To format it as a table:
We're going to insert a couple of columns for our new properly formatted first and last names
Add First Formula
We're going to use a formula for proper
Add Second Formula
Next let's repeat it for the last names
And there you go!
When people enter their contact information in online forms, the data you collect might be messy.
This contact list has text in upper, lower, proper and mixed case.
NOTE: The PROPER function only works correctly on names that have a single capital letter, at the start of each word in the name. For example, "de Bruin" is the correct spelling, but PROPER would change that to "De Bruin".
The LOWER function changes any uppercase letters in a text string to lowercase.
Note: The LOWER function does not have any effect on other types of characters -- it only changes letters.
There is only one argument in the LOWER function syntax, and it is required:
In this example, the LOWER function is used in a named Excel table, and a structured table reference is used in the formula:
In the EmailFix column, all letters in the email addresses are changed to lowercase.
The UPPER function changes any uppercase letters in a text string to uppercase.
Note: The UPPER function does not have any effect on other types of characters -- it only changes letters.
There is only one argument in the UPPER function syntax, and it is required:
In this example, the UPPER function is used in a named Excel table, but a normal cell reference is used in the formula:
In the CodeFix column, all letters in the product codes are changed to uppercase.
If people enter their names in a sign-up form, you might end up with a mixture of upper and lower case letters. You can see an example in the screen shot below
Your challenge is to clean up that list. Using Excel formulas, make these changes to the list:
There are solutions in the next section
In the previous section, your challenge was to clean up a simple list of names, using Excel formulas. I posted this challenge in my Excel newsletter too, and readers sent in their solutions.
To see the different solutions, including some very interesting approaches, download the completed Name Case Solution workbook. The zipped file is in xlsx format, and does not contain any macros.
PROPER Video: Download the PROPER function sample file to follow along with the video. See how to use the Excel PROPER function to clean up the first and last names, and make them consistent. The zipped file is in xlsx format, and there are no macros in the file.
Case Functions: Download the Case functions sample file with PROPER, LOWER and UPPER examples. The zipped file is in xlsx format, and there are no macros in the file.
Last updated: November 8, 2022 8:03 PM