Excel 2007 : Validation of ID number

[john1980]

Im having a bit of trouble trying to find the right formula for validating an ID number for Customer ID, which is C0000001 to C9999999

I tried going into the help section and experimented with the formula there but I cant get it to work. I also need to use the validation of different IDs such as S001 to S999.

Help would be appreciated. Thanks.

[Paul]

Hi John, welcome to the forum.

For the first validation, try the following Custom formula:

=AND(LEFT(A1,1)="C",ISNUMBER(RIGHT(A1,7)+0),LEN(A1)=8)

LEFT(A1,1)="C" checks to see if the first character is a C
ISNUMBER(RIGHT(A1,7)+0) checks to see if the last 7 characters constitutes a number (if there's any letters in there, it will fail)
LEN(A1)=8 checks to make sure the cell value is 8 characters long (1 letter + 7 digits)

The same method can be applied for S001 to S999, just change the three sections appropriately.

[NBVC]

Also, if you want to ensure the "C" is upper case:

=AND(EXACT(LEFT(A1,1),UPPER("C")),ISNUMBER(RIGHT(A1,7)+0),LEN(A1)=8)

[john1980]

Great it works.
Thanks paul for explaining what each part of the formula does.
Thanks NBVC for the tip.