If you need to change the case of text, such as people and product names, flora and fauna, and so on, to proper, uppercase, or lowercase, you don’t have to do a lot of retyping. In most cases, Flash Fill can do the work for you.
Here’s a list of names in lower case in column A. But let’s change the names to proper case, with the first letter in each name capitalized.
- In cell B2, in the column next to your data, enter the first and last name in proper case, and press Enter.
- Press Ctrl+E, or click Data > Flash Fill.
Flash Fill automatically fills in the rest of the names in column B, following your lead, in proper case.
Flash Fill converts text to proper, lower, or upper case. Type text the way you want it just once in the column next to the text you want changed, and Flash Fill takes care of the rest for you.
There’s more that you can do with Flash Fill: Split a column of data based on what you type.
Inconsistent capitalization? Use functions
Flash Fill works best on consistent data. For example, Flash Fill doesn’t work on text that’s mistyped like this.
With inconsistent text, use functions in formulas to change the case of existing text, without retyping. All three of these functions work the same, so we’ll demonstrate how to use just one.
Enter a formula using the PROPER function
We’ll change the case of the names to proper case using the PROPER function in a formula.
- In cell B1, type =PROPER(A1), and then press Enter to change Annik’s name to proper case.
- Notice the small green square in the lower-right corner of cell B1? Double-click the green square to copy the formula down through cell B5.
Now the names in column B are all in proper case.
Get rid of the first column
To complete the process, remove the column of mistyped names. But instead of just deleting the column you don’t need, complete the following steps to avoid formula errors (#REF!).
- Copy the names in column B by dragging down the column and pressing Ctrl+C.
- Then right-click cell A1.
- Click Paste Special, and then click Values > OK to paste the names in column A without the formulas, which you no longer need.
- Now you don’t need column B, so drag down the column to select it.
- Right-click the selection, and pick Delete > Entire Column > OK.