In this article I'd like to tell you about different ways to change Excel uppercase to lowercase or proper case. You'll learn how to perform these tasks with the help of Excel lower/upper functions, VBA macros, Microsoft Word, and an easy-to-use add-in by Ablebits.
The problem is that Excel doesn't have a special option for changing text case in worksheets. I don't know why Microsoft provided Word with such a powerful feature and didn't add it to Excel. It would really make spreadsheets tasks easier for many users. But you shouldn't rush into retyping all text data in your table. Fortunately, there are some good tricks to convert the text values in cells to uppercase, proper or lowercase. Let me share them with you.
Microsoft Excel has three special functions that you can use to change the case of text. They are UPPER, LOWER and PROPER. The upper() function allows you to convert all lowercase letters in a text string to uppercase. The lower() function helps to exclude capital letters from text. The proper() function makes the first letter of each word capitalized and leaves the other letters lowercase (Proper Case).
All three of these options work on the same principle, so I'll show you how to use one of them. Let's take the Excel uppercase function as an example.
Your formula should look like this =UPPER(C3)
, where C3 is the cell in the original column that has the text for conversion.
As you can see in the screenshot above, cell B3 contains the uppercase version of the text from cell C3.
Now you need to copy the formula to other cells in the helper column.
So you have two columns with the same text data, but in different case. I suppose you'd like to leave only the correct one. Let's copy the values from the helper column and then get rid of it.
Since you need only the text values, pick this option to avoid formula errors later.
Here you are!
This theory might look very complicated to you. Take it easy and try to go through all these steps yourself. You'll see that changing case with the use of Excel functions is not difficult at all.
If you don't want to mess with formulas in Excel, you can use a special command for changing text case in Word. Feel free to discover how this method works.
Now you've got your Excel table in Word.
Now you have your table with the text case converted in Word. Just copy and paste it back to Excel.
You can also use a VBA macro for changing case in Excel 2010 / 2013. Don't worry if your knowledge of VBA leaves much to be desired. A while ago I didn't know much about it as well, but now I can share three simple macros that make Excel convert text to uppercase, proper or lowercase.
I won't labor the point and tell you how to insert and run VBA code in Excel because it was well described in one of our previous blog posts. I just want to show the macros that you can copy and paste into the code Module.
If you want to convert text to uppercase, you can use the following Excel VBA macro:
Sub Uppercase() For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = UCase(Cell.Value) End If Next Cell End Sub
To apply Excel lowercase to your data, insert the code shown below into the Module window.
Sub Lowercase() For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = LCase(Cell.Value) End If Next Cell End Sub
Pick the following macro if you want to convert your text values to proper / title case.
Sub Propercase() For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = _ Application _ .WorksheetFunction _ .Proper(Cell.Value) End If Next Cell End Sub
Looking at the three methods described above you might still think that there is no easy way to change case in Excel. Let's see what the Cell Cleaner add-in can do to solve the problem. Probably, you'll change your mind afterwards and this method will work best for you.
After the installation the new Ablebits Data tab appears in Excel.
The Change case pane displays to the left of your worksheet.
With Cell Cleaner for Excel the changing case routine seems to be much easier, doesn't it?
Besides changing text case Cell Cleaner can help you to convert numbers in the text format to the number format, delete unwanted characters and excess spaces in your Excel table. Download the free 30-day trial version and check out how useful the add-in can be for you.
I hope now that you know nice tricks for changing case in Excel this task will never be a problem. Excel functions, Microsoft Word, VBA macros or Ablebits add-in are always there for you. You have a little left to do - just choose the tool that will work best for you.
131 responses to "4 ways for changing case in Excel 2019, 2016, 2013, 2010"
Capitalize first letters awesome..........:)
Never knew about the PROPER function .. thanks
Your blog is Not useful, its not that much complex, you just need to select a cell and press shift+f3
Hello!
The Shift+F3 shortcut changes text case in Word. In Excel, it opens the Insert Function dialog box.
Ouch she's right, slaughtered you.
Thanks a lot for the helpful post!
Most helpful post...thanks
Thanks for your post & simple explanations.
THANK U VERY MUCH
thanks for this please i will like to be more expert in Excel how can i be upgrading myself