Upper Case and Max 3 letters

  • Thread starter Thread starter razlan
  • Start date Start date
R

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.
 
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)))
 
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.
 
test

--
Biff
Microsoft Excel MVP


razlan said:
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.
Click to expand...
 
Back
Top