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

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

--
HTH,

RD

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

"Dan" wrote in message
...
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.
--
Dan

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)

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
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.
--
Dan

ISNUMBER(--RIGHT(A1,5))

That will accept the following:

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

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
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)))

--
HTH,

RD

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

"Dan" wrote in message
...
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.
--
Dan

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
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.
--
Dan

You're right ... stupid of me!

Neat formula.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Code() doesn't really need Left().

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
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.
--
Dan

On Fri, 25 Apr 2008 08:52:00 -0700, Dan wrote:

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),A ND(CODE(A1)=97,CODE(A1)<=122)),
ISNUMBER(-MID(A1,{2,3,4,5,6},1)))
--ron

"Ron Rosenfeld" wrote in message
...
On Fri, 25 Apr 2008 08:52:00 -0700, Dan wrote:

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),A ND(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 DataValidation rule (which is my
interpretation) you can't use formulas with array constants.

--
Biff
Microsoft Excel MVP

On Fri, 25 Apr 2008 21:58:51 -0400, "T. Valko" wrote:

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

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

Perfect! Thank you very much.
--
Dan


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Code() doesn't really need Left().

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
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.
--
Dan

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
Perfect! Thank you very much.
--
Dan


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Code() doesn't really need Left().

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Dan" wrote in message
...
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.
--
Dan