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!

 

[PeteWright]

Is there a fixed number of digits?

If, for example, the extension numbers are all the same length, you can use custom number formatting like this:

 

[jamways]

Is there a fixed number of digits?

If, for example, the extension numbers are all the same length, you can use custom number formatting like

 

[PeteWright]

Is that what you need?

 

[jamways]

Is there a fixed number of digits?

If, for example, the extension numbers are all the same length, you can use custom number formatting like this:

Sorry I should have said, there isn't a fixed number of digits, some are 2 digits in lengths and some are up to 6 digits. There will also be new entries wherein I cant be sure how many digits there will be. Apologies for my above blank post too.

Book1
	A
1	Telephone number 1 extension
2	0153
3	351
4	1
5	9656
6	16313
7	05
8	687
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	Custom	=ISNUMBER(A2)

 

[PeteWright]

Sorry I should have said, there isn't a fixed number of digits, some are 2 digits in lengths and some are up to 6 digits. There will also be new entries wherein I cant be sure how many digits there will be. Apologies for my above blank post too.

Book1
	A
1	Telephone number 1 extension
2	0153
3	351
4	1
5	9656
6	16313
7	05
8	687
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A8	Custom	=ISNUMBER(A2)

one more question.
How many leading zeros can there be?
Is there only maximum one leading zero?

 

[jamways]

one more question.
How many leading zeros can there be?
Is there only maximum one leading zero?

In my current data I only have one leading zero at the most, but with future entries I really cannot be sure.

 

[Marcelo Branco]

Maybe...

Custom criteria
=ISNUMBER(--A2)

M.

 

[PeteWright]

Maybe...

Custom criteria
=ISNUMBER(--A2)

M.

Nice one! What exactly does the double negative do?

My formula would be:

=ISNUMBER(NUMBERVALUE(A2))

 

[Marcelo Branco]

Nice one! What exactly does the double negative do?

converts a text to a number - if numeric otherwise results on error

M.