Who Says You Can't Easily Change Excel's Text Case?

BY STANLEY ZAROWIN

In the November 2004 Tech Q&A column (page 85) I wrote with absolute confidence that there is no easy way to change the case of text in Excel. Within three weeks of the publication date, 435 readers—all obviously better informed than I—very politely corrected me. And each took the time to guide me through the process. Thanks to all of you for that help. So, for those who don’t know how to change case in Excel, here’s how it’s done.

You have three conversion options: lower case, UPPER CASE and Title Case. If, for example, the lower case target text is in A1 and you want to change it to all upper case, prepare any other cell (A2), where the upper case text will eventually appear, and type =UPPER(A1).

A2 will automatically copy the text from A1 and upper case it, as shown in the screenshot below:

If you then want the changed text to appear in A1, just copy it from A2.

To change to lower case, use the formula =LOWER(A1) . And to convert to proper case, use =PROPER(A1) .

If you want the changed text to remain in the original cell, click in the cell that contains the text to automatically reproduce it in the formula box. Then go to the formula box and build the following formula around that text so that it eventually looks like this: =PROPER(“I can change the case of text in excel”).

Make special note that the text must be enclosed in double quotes (“).

One reader remembered that I recently suggested a shortcut for changing case in Word using the Insert Function (Shift+F3). With some adjustment, it turns out, that shortcut can be made to work in Excel, too. Here’s how.

Starting with the text in A1, I can change the text in excel , place your cursor in a blank cell, A2, and press Shift+F3, evoking this screen:

Under Select a function , look for the command you want: UPPER (or LOWER or PROPER ). If it’s not there, type in the command under Search for a function and click on Go . That adds the command to the list under Select a function . Highlight UPPER and click on OK , sending the command to the formula box ( fx ).

Now that you have the uppercase command in A2, click in A1; that tells Insert Function where to find the text that is to be copied and changed into upper case. Then click on OK , and the final product looks like this:

There are other ways to use the Shift+F3 function that you may wish to experiment with, but I find this method the easiest and fastest.

SPONSORED REPORT

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.