Upper or Lower case

[Ed Cones]

Is there a function that'll tell me whether a single character in a cell is
upper or lower case? IsUpper() would be nice, but it ain't there

Currently I'm just doing a case-sensitive sort to isolate them, but I'm
hoping for something quicker.

thx

 

[Ron Rosenfeld]

Is there a function that'll tell me whether a single character in a cell is
upper or lower case? IsUpper() would be nice, but it ain't there

Currently I'm just doing a case-sensitive sort to isolate them, but I'm
hoping for something quicker.

thx

=EXACT(A1,UPPER(A1))

will return true if the letter in A1 is uppercase; otherwise it will return
FALSE.
--ron

 

[Tyro]

If your single character is in A1 you could this to determine if the
character is upper case: =AND(CODE(A1)>=CODE("A"),CODE(A1)<=CODE("Z"))
That returns TRUE if the character is upper case, FALSE if not. Or you could
adapt it to look at specific characters in a word by using the LEFT, RIGHT
or MID
functions.

Tyro

 

[Gord Dibben]

Note: you can use Ed's formula in Format>Conditional Formatting>Formula is to
color any UPPER cases.


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

What was Ed's formula? I don't see it in my reader. But I believe you can use
my formula also in CF.

Note: you can use Ed's formula in Format>Conditional Formatting>Formula is to
color any UPPER cases.


Gord Dibben MS Excel MVP

--ron

 

[Ed Cones]

Thanks Ron. Exactly what I needed -- no pun intended.

 

[Ron Rosenfeld]

Thanks Ron. Exactly what I needed -- no pun intended.

You're welcome. Thanks for the feedback.
--ron

 

[Ed Cones]

Ah. This solution would test if it's within the range of A~Z as well.
Useful. Thanks to you as well.

 

[Tyro]

The formula =EXACT(A1,UPPER(A1)) returns TRUE if A1 has the text "1",
numeric 1, a period or A1 is empty etc.

Tyro

 

[Ed Cones]

I used =IF(ISBLANK(D1),1,IF(EXACT(D1,UPPER(D1)),0,1)), then sorted on the
column to float the upper-case cells to the top. Both your solutions work
well. Many thanks.

 

[Ron Rosenfeld]

I used =IF(ISBLANK(D1),1,IF(EXACT(D1,UPPER(D1)),0,1)), then sorted on the
column to float the upper-case cells to the top. Both your solutions work
well. Many thanks.

If you want to return a 1 or a 0, depending on if there is a capital letter,
versus anything else, you could use this formula also:

=SUMPRODUCT(--EXACT(A1,CHAR(ROW($65:$90))))


--ron

 

[Ed Cones]

Jeez! Made me think ;o)

I now understand way more about the row() function, the sumproduct()
function and arrays. thx.