Validating a Number ID

  • Thread starter Thread starter Ruan
  • Start date Start date
R

Ruan

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
 
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,CODE(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
Click to expand...
(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
 
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
 
Hi!
...doesn't seem to work for option 1, which is a straight
10 digit numeric ID
Click to expand...

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
 
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
 
Hi!

Are you using this as a data validation rule via
Data>Validation, or, are you simply using this formula in
a cell to return either TRUE or FALSE?

The reason I ask is that as a DV rule, the formula is
almost to it's length limit of 255 chars. A cell formula
can be 1024 chars long.

I don't understand why you're using PROPER. Can you
provide more detail as to how this is being used?

Biff
 
I am not using data validation.

Column A has all the IDs that where entered by Users. We have about 3,500
IDs. Column B has the Proper formula and Column C has your formula u gave
me. Sometimes Users don't use Upper Case, so I am using Proper just to
convert to Upper Case.

Ruan
 
Hi!

OK, if column B contains this formula: =PROPER(A1)

Then the validation formula references column B, so

Change this portion of the formula:

=OR(AND(ISNUMBER(B1),......

To:

=OR(AND(ISNUMBER(--B1),

That will take care of the PROPER issue.

Biff
 
Back
Top