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

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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))
 
Try putting the formula suggested by Harald Staff in conditional
format, that should do the trick
Ilan
 
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))
Click to expand...

=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)
 
=EXACT("@#$%&*! details!"
Click to expand...
Hey said:
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.
Click to expand...

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...
 
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
 
(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
 
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
 
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.
 
Back
Top