Alphanumeric limitation

[Dan]

Looking for help to validate a single cell to an alphanumeric entry that is
exacly 6 characters in length with the first character required to be a
letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect.

 

[RagDyer]

This works for alpha being upper case *only*:

=AND(LEN(A1)=6,ISNUMBER(--RIGHT(A1,5)),CODE(A1)>=65,CODE(A1)<=90)

To accept upper *or* lower case alpha:

=AND(LEN(A1)=6,ISNUMBER(--RIGHT(A1,5)),
OR(AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(A1)>=97,CODE(A1)<=122)))

 

[T. Valko]

This works based on the following conditions:

Length must be 6 characters
The 1st character must be upper case A-Z
Chars 2 through 6 must be numbers 0-9

=AND(LEN(A1)=6,CODE(LEFT(A1))>=65,CODE(LEFT(A1))<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5)

 

[T. Valko]

ISNUMBER(--RIGHT(A1,5))

That will accept the following:

A1 = X1.234
A1 = X-1234
A1 = X123E1

 

[RagDyer]

Code() doesn't really need Left().

Besides, can't get your formula to accept:
A12345

 

[T. Valko]

Code() doesn't really need Left().

Yeah, that'll save a few keystrokes.

=AND(LEN(A1)=6,CODE(A1)>=65,CODE(A1)<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5)

can't get your formula to accept: A12345

If you're testing it as a worksheet formula it has to be array entered.

 

[RagDyer]

You're right ... stupid of me!

Neat formula.<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Code() doesn't really need Left().

Yeah, that'll save a few keystrokes.

=AND(LEN(A1)=6,CODE(A1)>=65,CODE(A1)<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5)

can't get your formula to accept: A12345

If you're testing it as a worksheet formula it has to be array entered.

 

[Ron Rosenfeld]

Looking for help to validate a single cell to an alphanumeric entry that is
exacly 6 characters in length with the first character required to be a
letter and the remaining characters to be numbers. Exp: A00142 orT10001, ect.

=AND(LEN(A1)=6,CODE(A1)>=65,CODE(A1)<=90,ISNUMBER(-MID(A1,{2,3,4,5,6},1)))

if the first letter must be capitalized.

If it can be upper or lower case, then:

=AND(LEN(A1)=6,OR(AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(A1)>=97,CODE(A1)<=122)),
ISNUMBER(-MID(A1,{2,3,4,5,6},1)))
--ron

 

[T. Valko]

=AND(LEN(A1)=6,CODE(A1)>=65,CODE(A1)<=90,ISNUMBER(-MID(A1,{2,3,4,5,6},1)))

if the first letter must be capitalized.

If it can be upper or lower case, then:

=AND(LEN(A1)=6,OR(AND(CODE(A1)>=65,CODE(A1)<=90),AND(CODE(A1)>=97,CODE(A1)<=122)),
ISNUMBER(-MID(A1,{2,3,4,5,6},1)))
--ron

If the OP's intention is to use this as a Data>Validation rule (which is my
interpretation) you can't use formulas with array constants.

 

[Ron Rosenfeld]

If the OP's intention is to use this as a Data>Validation rule (which is my
interpretation) you can't use formulas with array constants.

Good point. I did not consider that.
--ron

 

[Dan]

Perfect! Thank you very much.
--
Dan

Code() doesn't really need Left().

Yeah, that'll save a few keystrokes.

=AND(LEN(A1)=6,CODE(A1)>=65,CODE(A1)<=90,COUNT(-MID(A1,ROW(INDIRECT("2:6")),1))=5)

can't get your formula to accept: A12345

If you're testing it as a worksheet formula it has to be array entered.

 

[T. Valko]

You're welcome. Thanks for the feedback!