Learn proper case formulas including the lower case formula and the upper case function. Fix imported or poorly organized data in Excel to make it readable. This example will show you how to use all three functions effectively and make a messy spreadsheet much more clean.
When you open the spreadsheet, there will be three columns to fix: the ID numbers, the names, and the email addresses. As you can tell, each has varying capitalization that do not match up and make some pieces harder to read. Your goal is to fix this issue by using these formulas.
Let’s start with fixing the names in the D column. As you can tell, they are not properly capitalized for names. Go to cell G3 and type:
D3’s content will now show in cell G3, but with proper capitalization. Drag the corner of cell G3 down to G7 and it will apply this formula to each name, fixing them.
Now you want to fix the email addresses as they have varying capitalization. Go to cell H3 and type:
This will make all the letters lowercase, and as you drag cell H3 down, it will do the same to all the other email addresses.
Finally, you’ll work with the uppercase function to fix the ID numbers and make the letters contained all in capitals. Click cell I3 and enter:
Then drag C3 down as you’ve done before, and each ID in the I column will have capital letters.
You can now delete the old columns with incorrect capitalization and use the correct columns instead. This will make it easier and more simple to read and export data. Just copy and paste the correct information into the beginning columns.
Learn more about Excel with our guides and tutorials, from beginner to advanced.
Check this out while you wait!