There are few things as frustrating as working in a spreadsheet with text which is in the wrong case for your needs. For example, you might have a spreadsheet where a list of names is all upper case, all lower case, or some mixture of both. If you have just a few names, change them manually is an option, but if you’re short on time, or you’re dealing with a lot of data, you need another solution.
Fortunately, Microsoft Excel has the solution built in for you! There are three formulas in Excel which deal with text case: UPPER, LOWER, and PROPER. For example, you might have a list like this:
As you can see, some of the movie names are proper case (each word capitalized) while several of the movie titles are uppercase. We’d like to normalize to one case format for the list. Here’s an example of how each formula would format a particular movie name:
As you can see, UPPER converts the cell into uppercase. LOWER converts the text to all lowercase. Finally, PROPER converts the text to Proper Case. As you can see, Proper Case makes the first letter of each word in Upper Case while the rest of the letters are lowercase. It is not a “smart” casing, the word “Of” is not typically capitalized, but PROPER doesn’t differentiate words like this, so you will need to manually fix this.
To fix our list to PROPER case, we’re going to enter =PROPER(A2) in the cell B2:
Now that the formula is in the first row of data, we want to fill it down to all of the cells in this column that are adjacent to a movie title. You can either select the Fill Down from the ribbon at the top, or take advantage of an Excel shortcut to fill down. In the bottom right hand corner of the B2 cell, there is a little black box (when you have cell B2 selected):
When you move your mouse over the little black box, it will turn into a +. When your cursor is in the black box and is a + shape, double-click your left mouse button. This will auto-fill down all of the cells with the proper formula referencing the cell to the left, like this:
Now, all of the cells have proper case in the B column. Unfortunately, these are just formulas and not the actual values yet. We can Paste Special the formulas and turn them into values. Select the B Column:
Select Copy or press Ctrl+C, then select the A Column and select the Paste pull down menu and select Paste Values:
Now, you have overwritten the A column with the values of the B column. You will need to re-enter the title in A1. You can now select the B column and delete it.
Congratulations! You now know how to use formulas to convert text into different cases.
