Data Validation number with leading zero

[kashif.special2005]

Hi,

I applied data validation in a cell that, the value should be number and should be 9 digits.

Data validation formula:

=AND(ISNUMBER(C5),LEN(C5)=9)

But the problem is, it is not accepting 9 digits with leading 0.

like,

Working:- 123456789
Not working:- 012345678

I want to accept, if I enter 9 digits with 0.

Please help to me resolve this problem

Thanks
Kashif

 

[Joe4]

The issue is that a leading 0 means nothing to actual numbers, and are dropped upon entry, even if you have custom formatting to show the leading zero. Data Validation looks at what is actually in the cell, not how it is formatted. To see what is actually in the cell, select it and see what shows up in the formula bar.

If you want to maintain leading zeroes, this cell will need to be formatted as Text, not as any number or custom number format. You can still do that and apply the Data Validation you want with a minor tweak:
=AND(ISNUMBER(C5+0),LEN(C5)=9)

By adding zero, the entry is converted from Text to numeric for the sake of that part of the validation.

 

[kashif.special2005]

Hi Joe,

Thank you so much for your help, it is working fine like magic.

Thank you so much from all of my heart, long live Jeo

 

[Joe4]

You are most welcome. Glad to help!