Upper Case and Max 3 letters

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.
 
B

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)))
 
R

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

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top