Hi!

Does it matter if the alpha char is upper or lower case? I
see in your examples they're upper case.

This will work for upper case:

=OR(AND(ISNUMBER(A1),LEN(A1)=10),AND(LEN(A1)=10,CO DE(MID
(A1,9,1))=65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE
(A1,MID(A1,9,1),""))),AND(LEN(A1)=14,CODE(MID(A1,9 ,1))
=65,CODE(MID(A1,9,1))<=90,ISNUMBER(--SUBSTITUTE(A1,MID
(A1,9,1),""))))

If you want to accept lower case, then just change the
CODE values to =97 and <=122.

If you want either upper or lower, drop back and punt!

You could use the CODE range from =65 and <=122 but that
would leave open the possibilty that CHARS 91 through 96
could be entered.

Biff

-----Original Message-----
Hello,

I am struggling to write a formula to validate an ID
Number.

Here are the valid options -
1) All numeric 10 character ID (example: 1234567890)

2) Alphanumeric 10 character ID, with the 9th character
being a Alpha
character (example: 12345678E0)

3) Alphanumeric 14 character ID, with the 9th character
being a Alpha
character (example: 12345678A01234)


The following examples have been used, but are not
acceptable -
1) 1234567*9
2) 1234567*901234
3) 12345678'9
4) 12345678,9


Any help will be greatly appreciated.
Thanks
Ruan




.