Quick and easy ways to change case text in Excel.
You may have a list which you wish to change the case test in Excel. Rather than manually re-entering the data in Excel, there are some useful functions you can call on to save you this work. Let’s look at a few of them.
In this example we have a list of planets starting in lowercase and we wish to have them starting in uppercase. Here we will use the Proper function.
Step 1 Select the adjacent blank cell and type =Proper(A1)
Step 2. Autofill through to B8. You should now have a list of Planets, not planets!
Step 3. To replace the original data copy the cells B1:B8 and paste as values back to A1:A8.
Step 4. Finally clear the range B1:B8.
To turn text to upper case repeat the steps above using the Upper function instead of Proper. And, yes you guessed it, use the Lower function to change test all to lowercase.
Writing a Macro to run the Proper case function
If you need to change the case of text in Excel on a routine basis, you can save even more time by automating the process with a quick macro.
Using a blank Workbook press Alt+F11 to go to the VBA editor.
Insert a new module and type the following 2 macros.
Sub Proper()
Dim cell As Range ‘cell is a name for the range variable
For Each cell In Selection
cell = WorksheetFunction.Proper(cell)
Next cell
End Sub
Sub Upper()
Dim cell As Range
For Each cell In Selection
cell = UCase(cell)
Next cell
End Sub
Now you can close the “Microsoft Visual Basic for Applications” window (don’t worry your macros are now saved), and return to your spreadsheet.
Save your spreadsheet as a Macro-Enabled workbook called MyMacros.
You could use the workbook to store other macros for use in any workbook and assign them to buttons on the Quick Access Toolbar.
Finally hide the workbook by selecting View, Hide and exit Excel saving again as MyMacros.
When you return to Excel open MyMacros and add the Proper and Upper macros to the Quick Access Toolbar as follows.
1. Click the arrow on the right of the Quick Access Toolbar.
2. Select More Commands.
3. Choose Macros in the commands from option.
4. Click Proper and click Add, click Upper then Add.
5. Select Modify to change the button images.
6. Change the Display name.
6. Reposition the buttons to suit.
Now exit Excel again and test your macros work by selecting some text.
The hidden MyMacros workbook automatically opens when the Proper Case button is clicked and changes the text accordingly. A press of the button and the work is done, whether for a single cell or for a large range of selected cells in any workbook, saving you time and the risk of errors from manual data entry.
