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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

COLUMN

Deflecting clients’ requests for defense and indemnity

Client requests for defense and indemnity by the CPA firm are on the rise. Requests for such clauses are unnecessary and unfair, and, in some cases, are unenforceable.