Upper Case and Max 3 letters

[razlan]

How do I limit a cell (i.e F51) to this criteria when user key in data
in it?

a) All letters are Upper Case
b) Maximum number of letter is 3

I managed to add this formula in Data Validation to make it all Upper
-
=EXACT(F51,UPPER(F51))

But how do I limit the user to a maximum of 3 letters only (and no
number allowed)?

Thank you.

 

[Bob Phillips]

Try

=AND(LEN(F51)=3,NOT(ISNUMBER(--LEFT(F51,1))),NOT(ISNUMBER(--MID(F51,2,1))),NOT(ISNUMBER(--RIGHT(F51,1))),EXACT(F51,UPPER(F51)))

 

[razlan]

Thank you, Bob Philips.
The formula works great. You are brilliant.

Just wondering if I can add one more criteria.
In that cell F51, the data should not be the same as any data (no
duplication/repetition) in cells B110:B241.

It works with this formula if set alone, but can I add this to ur
formula? =COUNTIF(B110:B241,F51)=0

Thank you.

 

[T. Valko]

test

--
Biff
Microsoft Excel MVP

Thank you, Bob Philips.
The formula works great. You are brilliant.

Just wondering if I can add one more criteria.
In that cell F51, the data should not be the same as any data (no
duplication/repetition) in cells B110:B241.

It works with this formula if set alone, but can I add this to ur
formula? =COUNTIF(B110:B241,F51)=0

Thank you.