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.
Read More
No comments:
Post a Comment