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

[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

 

[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 :

 

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

 

[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

 

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

 

[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$"

)

 

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

 

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

 

[isabelle]

Ron, you're right, your formula is the best, mine accepts the "& $ * [ ] ..."

 

[Ron Rosenfeld]

Ron, you're right, your formula is the best, mine accepts the "& $ * [ ] ..."

Uh, that was Rick that devised the formula.

 

[isabelle]

oops sorry, then thank you both

--
isabelle



Le 2011-12-14 16:46, Ron Rosenfeld a écrit :

Ron, you're right, your formula is the best, mine accepts the "& $ * [ ] ..."

Uh, that was Rick that devised the formula.