Data Validation of ID Number to accept Leading Zeros

[PeterDavids]

Dear All
I have a column in which I input ID numbers. The data validation checks for the correct format as well as for duplication of numbers in the same column.
This works fine until ID numbers with leading zeroes are input, the validation formula rejects this number,
Below is the validation formula as well as the sample ID number that is rejected by the formula:

=AND(ISNUMBER(F8),LEN(F8)=13,COUNTIF($F$8:$F$701,F8)<=1)
and
0003115218087

The column is also formatted to display the ID Number as follows, 000000" "0000" "000
Could I please ask for assistance with a variation of the validation formula above to accept numbers with leading zeroes?
Thanks
Peter

 

[Toadstool]

Hi PeterDavids,

The format only changes the way you see it, not how it reacts in a formula. A numeric Id is generally a bad thing for the reason you've found, it can be misinterpreted and a common challenge is when it's converted to text but then compared to a numeric Id where the match will fail, even if both look like 3115218087. If you have the opportunity to change it I would do so, just prefix with "X" and it will be treated as text.

Your LEN(F8)=13 fails because Excel sees 3115218087. If you want to stay with a numeric Id then the best check there would be LEN(F8)<=13

 

[PeterDavids]

Hi Toadstool

Thanks a lot for your workaround, LEN(F8)<=13 did the trick! Unfortunately I am stuck with the numeric Id as its a government issued personal id number given to every citizen.

Much appreciated.

 

[PeterDavids]

I forgot to ask, how do I mark this post as solved?

 

[Toadstool]

I'm not sure there's a way to mark a question as solved..