Data Validation

[Bob]

I want to limit entry in a cell to a 9 digit number. I
had have some success because I can enter the following
formula in Tools - Data - Validation - Custom and it is
almost successul:

=AND(LEN(C6)=9,C6=INT(C6),ISNUMBER(C6))

Except only if the the number begin with a zero:

i.e. 023564789 is not valid.

Any help.

 

[Leo Heuser]

Bob

If you enter 012 in a cell, the number is
a 2-digit number (12) to Excel, and therefore
your input has a length of 2.
You can either format the cell as "Text" or
enter as '012 to keep the length as 3.
Formatting the cell with e.g. 000000000
is not enough, since Excel works on the
underlying value.