Change a data range’s text case with 3 easy shortcut keys
by Kara Hiltz
Application:
Microsoft Excel 2002/2003/2007
Operating System:
Microsoft Windows
If you use Microsoft Word, you probably know that you can press [Shift][F3] to toggle a word’s case from uppercase to lowercase to proper case. But if you try to use that shortcut key in Excel, you’ll open the Insert Function dialog box. Instead of messing around with functions, we’ll show you how to assign your own shortcut keys to three simple macros — one for each case.
To create three shortcut keys that allow us to choose a data range and instantly change that range’s text case, we’ll:
• Set up a sample workbook with text in three different cases: lowercase, uppercase, and proper case.
• Create three macros — one for each case — and explain how the VBA works.
• Assign a memorable shortcut key to each macro so you can change text without leaving the keyboard.
Excel offers you three functions to change a cell’s text case — UPPER(), LOWER(), and PROPER() — but no shortcut key like Word offers. And converting text using functions can prove labor-intensive. You have to create a new column for the formula results and then paste them over the original data as values instead of formulas. Confused yet? Exactly. That’s why we’ve created three simple macros — one for each case — and will show you how to assign them shortcut keys for easy use.
Learn from example
We’ve created a sample worksheet with all kinds of different text cases to demonstrate how our macros work. The worksheet, as shown in Figure A, contains shipping information for several products. We want to convert the headers and locations into proper case, the product codes and shipping statuses into uppercase, and the notes into lowercase. It would take a lot of work if you used functions to make the changes
A:
This sample workbook includes text of all different cases that would take a lot of time to convert without our macros.
Add your first macro
The three macros we’ll use to convert a data range’s text into a different case are nearly identical. We’ll start with the macro that converts text into uppercase.
To build a macro that converts text into uppercase:
1. Launch Excel and open our sample workbook, or any workbook in which you want to change text case.
2. Press [Alt][F11] to open the Visual Basic Editor (VBE).
3. Choose Insert | Module from the VBE’s menu bar to open a blank code window.
4. Enter the code in Listing A or copy and paste the code from our download file.
Listing A:
Code to convert a data range’s text to uppercase
Sub ConverttoUppercase()
