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)

"Jay" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
> 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

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jay" <(E-Mail Removed)> wrote in message news:O$(E-Mail Removed)...
> 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

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




David McRitchie wrote:
> 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
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Jay" <(E-Mail Removed)> wrote in message news:O$(E-Mail Removed)...
>> 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
>
>