In this blog we will cover how to change the case of your text in Excel with or without a formula.
If you're a keen user of the Change Case button in Word, Outlook or PowerPoint you may be wondering where it is in Excel.
The Change Case button in Word
Let’s look at an example of where the Change Case button is in Word.
It is located on the Home tab (Aa).
Click on the drop down arrow and you will see all the different case options that are available to change your text.
Highlight the text you want to change and select the case option you want.
It's nice and easy in Word but where is it in Excel?
The Change Case button in Excel
In Excel there is no Change Case button on the Home tab.
So, how can you change your case in Excel?
Change case without functions
If you're in the newer versions of Excel, you can use Flash Fill.
Flash Fill will automatically fill in information in a list if you already have that information in your table. For example, the Surnames in column A.
If you start typing the same surnames in column D, Flash Fill will instantly drop down the surnames from column A.
Press your Enter key and it will add these surnames, giving you the same information as column A.
You can take it one step further and show Flash Fill what case you want all the surnames in. For example, green in column D7.
Overtype green to Green and press Enter.
Flash Fill will now change all the case in that column to the same as Green.
One thing that should be looked out for is Flash Fill can change the case of the word you have just written. For example, if you were to write Frank in cell E4 and Carole in cell E5.
Once you start typing Carole, Flash Fill will drop down all the other names.
It has changed Carole back to carole as this is the original case the name was in from column B.
If you don't get the case you want, do this.
Highlight over the information that has been filled in and delete it.
Overtype the information that has been changed back to what you want it to be. For this example, carole is being changed back to Carole.
Then highlight over the area where you want the information to be and include your two options at the top such as Frank and Carole.
Go to the Data tab.
Click the Flash Fill button on the right of the ribbon.
You should now have all your information copied over in the correct case.
Useful tip: Instead of using the Flash Fill button you can use the shortcut Ctrl + E.
This is great if you’re using the newer versions of Excel, but what if you’re using older versions such as Excel 2010 or older and don’t have the Flash Fill button?
Changing case in older versions of Excel using Word
Copy the information you want to change the case of by highlighting (for example, the first names in column B ) and pressing Ctrl + C or use a right mouse click and select Copy.
Go into a Word document and paste it using Ctrl + V or doing a right mouse click and selecting paste.
Your information should have pasted into Word as a table.
Highlight over this list and use your Change Case button to change the case of the text. You can select whatever case suits you.
Highlight the list and copy it.
In Excel, paste it where you want it to be. For example, column F in this worksheet.
How to change case in Excel with formulas
Functions can be used in order to change your case. For example, the LOWER, UPPER and PROPER functions.
LOWER function takes whatever is in a cell and converts it to lowercase text.
To do this, select the cell where you want the lowercase letters. For example, cell D4.
Useful Tip: Sometimes its beneficial to have a column next to the text you want to change case. For example, D4 is adjacent to C4 which has the text ‘Landscape services’ that we want to change to ‘landscape services’
Start by typing =LOWER(
Now, click onto the cell in which you want to change the case. For example, cell C4 in the example above.
Insert a closing bracket to give the formula =LOWER(C4)
You should have the text from the original cell (C4) all in lowercase in the new cell (D4).
In order to copy this formula down, double click the bottom right of the cell where the little green square is.
Useful Tip: When you start to type the name of a function Excel will show it in a quick tip under the cell. To drop it into your cell, you can press your Tab key so that you don’t have to type the word out completely.
You can follow these steps again for UPPER, where all of the text is changed to upper case and PROPER, where all of the case is set to proper case where each word will begin with a capital letter.
Was this Blog helpful? Let us know in the comments below.