Case changing and checking in Excel
June 18, 2009 • JP • No Comments • Rate This Article
• Links to this article
A co-worker of mine came to me with a problem. Say you have a column with mixed upper and lower text, and wanted to switch everything to upper case. The slow way would be to add a helper column, painstakingly insert individual UPPER formulas next to each lower case cell, then individually paste values back into the original column.
Or just use the following formula (and method).
It checks if the cell is already upper case and simply reprints it in the helper column, otherwise it applies the Upper function to the cell. Then, all you have to do is paste in the values and then copy the entire block of data over the original column (or delete the original column, if applicable), without worrying about which cells need to be copied and pasted. Much faster and easier than the "hunt and peck" method.
=IF(EXACT(A1,UPPER(A1)),A1,UPPER(A1))
This can work with lower and proper case as well, just substitute the words "LOWER" or "PROPER" as appropriate in the above formula. You could even just use =UPPER(A1).
Case checking
In Case Changing in Excel I presented a VBA based method for changing text case inside cells on a worksheet. Here are some formulas that may be used to check if a cell is a certain case.
Is proper case? (Exact method)
=EXACT(A1,PROPER(A1))
Is upper case? (Exact method)
=EXACT(A1,UPPER(A1))
Is Lower case? (Exact method)
=EXACT(A1,LOWER(A1))
And if needed, we can also check if specific parts of a text entry are upper or lower case, using the CODE function. These two examples check if the first letter of a cell is upper or lower case.
Is upper case? (Code method)
=AND(CODE(LEFT(A1,1))>=65,CODE(LEFT(A1,1))<=90)
Is lower case? (Code method)
=AND(CODE(LEFT(A1,1))>=97,CODE(LEFT(A1,1))<=122)
We could also use the Exact function, i.e. =EXACT(LEFT(A1,1),UPPER(LEFT(A1,1))).
If you want to learn the code values for each letter of the alphabet as shown above, just enter =CHAR(ROW()) in cell A1 and fill down. Rows 65 through 90 are the capital letters; rows 97 through 122 are the lowercase letters.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Create Tiny URLs using VBA
Next Post: Slick worksheet navigation without macros
Hi
There is no need for "hunt and peck" as you describe.
Just enter =UPPER(A1)
Double click fill handle to extend down the used range, then Copy>Paste Special>Values
The test for whether a value is already upper case is superfluous.
Roger,
You're right, and I did mention it briefly in the article.
Thx