Expert: Nathan Head - 3/28/2008

Question
I IMPORTED DATA TO AN EXCELL 2007 WORKSHEET. THE LETTERS ARE A MIX OF UPPER AND LOWER CASE, I WANT TO CHANGE ALL LETTERS TO UPPER CASE. I WANT TO CHANGE THE ENTIRE SHEET, NOT 1 CELL AT A TIME. HOW CAN I DO THIS

Answer
Mary:

If you are looking for a solution to change the entire sheet with just one click of a button, it doesn't exist; however, here are some options:

1) Excel has an UPPER() function.  If you have text in cell A1, then you can use this, =UPPER(A1), to convert that text to all uppercase.  Thus, if you have text in A1:C1000, then place this in cell D1: =UPPER(A1) and then copy that formula from D1 through F1000.  If you catch what I'm doing here, it is just duplicating your original data immediately to the right of the old data...just all in upper case.  Once you have done this, you can copy the new data, and then choose "edit->paste special->values" to clear out the UPPER formulas and you will simply be left with the text.

2) If possible, you could copy the data in Excel to Word (or import data into Word First).  Microsoft Word has a "change case" feature that will change all the text to Upper case.  Once that is done, if possible, you can copy the data back into Excel all at once (this option may not work very well, but I wanted to present it anyway since it could work in certain situations).

I'd go with #1 - it will actually be pretty quick and shouldn't take more than a few minutes.