Hello Biff,
I have been entering in 10 character numeric values and the result is
"False". The other 2 conditions work fine.
I think I might know what the issue is. To make sure that the 9th character
is uppercase, I am using the following to convert it -
=PROPER(A1)
This is obviously changing it to Text. How do I get around this?
Thanks
Ruan
"Biff" wrote in message
...
Hi!
...doesn't seem to work for option 1, which is a straight
10 digit numeric ID
It should work. That's the easiest condition to test for!
It works in my test file. Are you sure the 10 char string
is in fact a numeric value and not TEXT?
Biff
-----Original Message-----
Hello Biff,
The alpha character is upper case. The formula works
great for the valid
options 2 and 3, but doesn't seem to work for option 1,
which is a straight
10 digit numeric ID.
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)
Thanks so much for your help.
Ruan
"Biff" wrote in message
...
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
.
.
|