Learn to fix text in Excel that is not capitalized correctly. This guide will show you the three steps needed to fix any uneven or incorrectly capitalized text within an Excel spreadsheet. This is a great tool for when you import large amounts of data to Excel. The easy formulas will save you time when fixing spreadsheets with lots of information contained within. You can quickly apply these formulas to multiple cells at once, making this a very easy fix.
Download the example spreadsheet and learn this three-step process that is a real time saver.
The Excel file you open will contain a column of ID numbers, another of names, and a column of emails. All three of these columns contain formatting errors that can be fixed by a quick formula. We’re going to start at the farthest end formula – the upper() formula – and move backward.
The Upper Formula
This formula will take a specified cell, and create a completely capitalized version of it in another cell. This is helpful because it makes IDs much easier to read (as the example will show).
Go to cell I3 and type in:
=upper(C3)
In cell I3, it should read 942XT032. Now, take the corner of cell I3 and drag it down to I7 to apply this formula to all of the IDs.
The Lower Case Formula
Email addresses are much easier read in all lower case. This is what you will be doing in the H column. Select cell H3 and type:
=lower(E3)
It will now show in cell H3 the E3 email address in all lowercase. As you did before, drag the corner of cell H3 down to H7 to apply this formula to all emails on the list.
The Proper Name Formula
Some names may not be imported or entered properly, meaning the first or last name, or both, do not have their first letter capitalized. To remedy this, use the proper formula.
Select cell G3 and input:
=proper(D3)
G3 will now read “Dante Bright”. Drag the cell down the column as you’ve done before and it will fix all of the names.
From this point, you can delete the first 3 columns and just have the corrected columns available to export or use.
Check out this offer while you wait!