Hi Anne

You can try =TEXT(A1,"0000000000")

It works for both 9 or 10 digit numbers.

Please note that this will convert the numbers to text.

Otherwise you can custom format the cells with 0000000000 (10 zeroes)

Hope this helps.

Sunny

Thank you. 
this text formula works to add a 0 to all numbers, but not to the ones only containing 9 numbers. I don’t need a 0 in front of the cells with 10 numbers in it.  Is there a formula for this? 

Hi Anne,

The formula above won't add a 0 in front of numbers that contain 10 digits. Perhaps you can share your file so we can troubleshoot.

Mynda

Hi Anne

I suspect you keyed 11 zeroes instead of 10 in your formula.

The formula will only add a zero to the front of numbers with 9 or less digits.

Same with the custom formatting.

Sunny

Thanks for your responses. And I have to apologise, because the cells I need a zero in front of has 10 numbers ,not 9.

the cells I don’t need a zero in front of , has 9 numbers in it. 
It’s the opposite of what I said earlier . 

these are all in the same column. 

Ive tried the text formula and if I apply it to the 10 number cells, and copy the formula down , it will also give a zero in front of the cells with 9 numbers, which i don’t need. 
so I suspect I need a type of IF formula ?

Hi Anne,

Yes, an IF will work. The formula below will convert both 9 and 10 digit numbers to text:

=IF(LEN(A1)=9,TEXT(A1,"000000000"),TEXT(A1,"00000000000"))

If you're happy with a mix of numbers and text in the column then you can use this:

=IF(LEN(A2)=9,A1,TEXT(A1,"00000000000"))

Although I would encourage you to have consistent values, therefore the first formula would be best.

Mynda