Data Validation -- Numbers in Text Field

[CPGDeveloper]

I can't seem to figure this out -- any help would be greatly appreciated.

I'm trying to validate a column in my excel workbook with the following criteria:

Length of 9 characters
Each character must be a number
The first character may be '0'.
The field itself should remain a text field.

So both 012345678 And 123456789 would be valid.

I have the following custom data validation formula:

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

But of course, this would eliminate '012345678', considering it 8 characters, not 9.

Any ideas? Thanks.

 

[Joe4]

Try this:

=AND(ISNUMBER(B3+0),LEN(B3)=9)

 

[Rick Rothstein]

Try this:

=AND(ISNUMBER(B3+0),LEN(B3)=9)

I am guessing that for the kinds of values that will be entered into cell B3, your formula should be sufficient, but the OP should know that it is not fully robust as it will report TRUE for values like these...

April2018

1.23456E8

-1.23e-04