Checking for ALL Upper case?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Can anyone let me know how to check a cell for text in all upper case?

I have a column containing only text, some cells contain the
manufacturer & some the model. However the Manufacturer is in all upper
case. And the cells below that particular Manufacturer are the models of
that manufacturer.

So I am wanting to check for all upper case so I can get the related
manufacturer in the cell to the left of the model field, using an IF
statement.

What can I incorporate into an IF to check a cell for an all upper-case
text string?

Many TIA

Jason
 
=EXACT(UPPER(D16),D16)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Hi Jason,
if you have numbers mixed in you might want to use
=EXACT(A1,UPPER(A1))
in preference to
=A1=UPPER(A1)

In an IF Worksheet Formula
=IF(EXACT(A1,UPPER(A1)),"","upper")

within VBA, comparisons are automatically case sensitive.

Since you want something to left, and the names are text,
I think you want
A2: =IF(B2=UPPER(B2), B2, "")
or if you want to carry it down possibly
A2: =IF(B2=UPPER(B2), B2, A1)

To avoid some problems you might also want to include TRIM
in the formula -- if there is some sloppyness in the entries,
or run TRIMALL macro from my
http://www.mvps.org/dmcritchie/excel/proper.htm#trimall
 
Thanks a lot David, I really appreciate the suggestions. It's responses
like these (& Bob's) that really teach me new things because I go back &
try & use the functions & techniques whenever I can. The formula I
ended up with is similar to the last one you proposed. Mine is:

=IF(EXACT(UPPER(B1),B1),B1,IF(EXACT(UPPER(B2),B2),"",A1))

The second IF is to prevent putting the Manufacturer (Upper Case) to the
left of the next Manufacturer in the list. I only want the Upper Case
manufacturer to the left of non-upper case cells (model).

Although I did try it on a list containing numbers and it seemed to work OK.

Thanks again,

Jason
 
Back
Top