Limit a field to 3 digits 1 capital letter 1nd 2 numbers

J

jean

Hi

I want to use Data Validation to restric user for data entries

I need that the user use this format in column B: 1 letter in capital
and 2 numbers like A02 or S31 or T11 or M45 .....

I have try many custom formula without success

I would also would like that within a range in column B that it would
be without duplicate

Thanks for helping
 
I

isabelle

hi Jean,

=AND(LEN(A1)=3,NOT(ISNUMBER(--MID(A1,1,1))),ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,3,1)),EXACT(MID(A1,1,1),PROPER(MID(A1,1,1))))

--
isabelle



Le 2011-12-13 10:50, jean a écrit :
 
R

Rick Rothstein

I want to use Data Validation to restric user for data entries
I need that the user use this format in column B: 1 letter in
capital and 2 numbers like A02 or S31 or T11 or M45 .....

Give this a try....

=AND(LEN(A1)=3,CODE(A1)>64,CODE(A1)<91,ISNUMBER(--MID(A1,2,2)))

Rick Rothstein (MVP - Excel)
 
R

Ron Rosenfeld

Give this a try....

=AND(LEN(A1)=3,CODE(A1)>64,CODE(A1)<91,ISNUMBER(--MID(A1,2,2)))

Rick Rothstein (MVP - Excel)

Rick,

Your formula does not restrict the 2nd and 3rd characters to being digits. I think you have to check the ascii codes in order to do that.

-- Ron
 
R

Rick Rothstein

Your formula does not restrict the 2nd and 3rd
characters to being digits.

Hmm, you are right... that formula will pass things like A+1 and A2. as well
as others.
I think you have to check the ascii codes in
order to do that.

I think this way involves a couple of less tests...

=AND(LEN(A1)=3,CODE(A1)>64,CODE(A1)<91,ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,3,1)))

Rick Rothstein (MVP - Excel)
 
R

Ron Rosenfeld

=AND(LEN(A1)=3,CODE(A1)>64,CODE(A1)<91,ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,3,1)))

Yes, that is fewer tests. I was thinking regex equivalents. If one could use that in data validation, the expression would look like "^[A-Z]\d\d$"

)
 
R

Rick Rothstein

=AND(LEN(A1)=3,CODE(A1)>64,CODE(A1)<91,ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,3,1)))
Yes, that is fewer tests. I was thinking regex equivalents. If one could
use that in data validation, the expression would look like "^[A-Z]\d\d$"

Using the Like operator's patterns, it would look like "[A-Z]##"

Rick Rothstein (MVP - Excel)
 
R

Ron Rosenfeld

=AND(LEN(A1)=3,CODE(A1)>64,CODE(A1)<91,ISNUMBER(--MID(A1,2,1)),ISNUMBER(--MID(A1,3,1)))

Yes, that is fewer tests. I was thinking regex equivalents. If one could
use that in data validation, the expression would look like "^[A-Z]\d\d$"

Using the Like operator's patterns, it would look like "[A-Z]##"

Rick Rothstein (MVP - Excel)

Unfortunately, neither can be used directly in data validation.
 

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