Need formula to find cells that contain only lower case text entries

[Steve]

This may sound like a rediculous question, but does anyone know of a
formula that could be used to find cells that contain only lower case
text entries?


Please advise,

Steve

 

[Harald Staff]

Hi Steve

It doesn't really find A1, but once you found it yourself it tells you if
it's lowercase or not. Close enough ?
=EXACT(A1,LOWER(A1))

 

[Ilan Rencus]

Try putting the formula suggested by Harald Staff in conditional
format, that should do the trick
Ilan

 

[Harlan Grove]

It doesn't really find A1, but once you found it yourself it tells you if
it's lowercase or not. Close enough ?
=EXACT(A1,LOWER(A1))

=EXACT("@#$%&*! details!",LOWER("@#$%&*! details!"))

The string doesn't consist exclusively of lower case characters. If the OP means
cells that contain only lower case text if they contain any text, with all
nontext characters ignored, then EXACT(x,LOWER(x)) works. Otherwise, see below.

If you mean all characters in the cell must be lower case letters, then try
something like this. Define the following two names.

Seq referring to =ROW(INDIRECT("1:1024"))

LowerAlpha referring to ="abcdefghijklmnopqrstuvwxyz"

Then use formulas like

=ISNUMBER(SUMPRODUCT(FIND(MID(A1,Seq,1),LowerAlpha)))

To find the first cell in A1:A100 containing only lower case letters, try the
array formula

=MATCH(TRUE,MMULT(--ISNUMBER(FIND(MID(A1:A100,TRANSPOSE(Seq),1),LowerAlpha)),
Seq^0)=COUNT(Seq),0)

 

[Harald Staff]

=EXACT("@#$%&*! details!"

The string doesn't consist exclusively of lower case characters. If the OP means
cells that contain only lower case text if they contain any text, with all
nontext characters ignored, then EXACT(x,LOWER(x)) works. Otherwise, see below.

Good stuff as always, Harlan. But text vs charachers vs letters/digits, isn't this a
solution in search of a problem ? Like Ban "2" because it doesn't have a different
uppercase twin. What good could possibly that do for anything ? ...it's just my restricted
imagination perhaps. Select Y and click &Submit, I won't complain...

 

[Steve]

Harold,

Sorry for not getting back sooner, everyone is sick in my household...
Anyway, I wanted to let you know that your formula did work for me.
I did modifiy it slightly to eliminate the possibility of cells with
just numbers...

Here is what I used:

=IF(EXACT(A1,LOWER(A1)),IF(ISNUMBER(A1),"",A1),"")

This actually gave showed me what was in all the cells containing only
lower case text...


Thanks so much for your help.

Regards,

Steve

 

[Steve]

(e-mail address removed) (Ilan Rencus) wrote in message
Ilan,

Thanks for the suggestion, I will save it and consider it for use in
the future. For now, Harold's original solution (slightly modified
to eliminate cells with just numbers) seemed to do the trick.


Regards,

Steve

 

[Steve]

Harlan,

I've seemed to accomplish what I was after with a slightly modified
version of Harold's original formula to eliminate cells containing
only numbers. I will save your suggestions and experiment with them
in my spare time.

Thanks again,

Steve

 

[Harald Staff]

Thanks for the feedback, Steve. So Harlan was right and I'm even more confused. Making
everything perfectly normal around here. Cool

Good luck with your family.

Best wishes Harald
Followup to newsgroup only please.