How to add prefix "0" to Excel cell if the cell length is 10?

user_18816434 0
Viewed 51

I have an Excel column of phone numbers that should contain data on either:

  1. Mobile numbers in 11 digits like this: 01205468889
  2. Hotlines in 5 digits like this: 16007

The problem so many mobile numbers lost their first "0" due to cell formatting somewhere in data collection process.

I need to have a formula that can be applied to a second column to correct this by adding "0" to only cells which lost the first digit.

Something like: if the cell value is 10 digits add 0 first, then copy the value, else don't do anything.

How can I do this in Excel?

excel
2 Answers

You can try this; but result becomes a text, not a number if It satisfies the condition:

=IF(LEN(B2) = 10,"0"&B2,B2)

GFG

user_194690880

If the leading 0 must be in the value, marc_s is correct and it will be text. If you just need to display the 0, consider cell formatting "000000000000" and it will be a number.

user_198066230
Related