Log In
Register
Home
Active Member
November 22, 2019
Offline
Hi there,
so I have a column with 9 or 10 numbers in each cell.
i need to add a 0 (zero) only in front of the cells containing 9 numbers .
does anyone know an excel formula for this?
Thanks,
VIP
Trusted Members
June 25, 2016
Offline
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
Active Member
November 22, 2019
Offline
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?
July 16, 2010
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
Active Member
November 22, 2019
Offline
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 ?
July 16, 2010
Offline
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
1 Guest(s)
