Maintain leading zeros and only allow numbers

[jamways]

Hello everyone.

I have a column for telephone number extensions in my sheet. Of course, some of these extension numbers may begin with a zero. To keep the leading zero, I have changed the formatting of the column to 'text'. I also only want to allow numbers to be entered into the column.
However, in formatting the column to 'text', I am unable to maintain data validation for the rest of the column to only allow numbers. I was using the formula =ISNUMBER(cell) but this contradicts the 'text' formatting and I get validation errors when anything is posted.

Any help would be much appreciated!

 

[Marcelo Branco]

Warning:
This formula allows entries like
+078
1e+10
-89

A more robust solution would be
Custom
=SUMPRODUCT(--ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))=LEN(A2)

M.

 

[jamways]

Thanks so much, this worked perfectly!