I have an Excel column of phone numbers that should contain data on either:
- Mobile numbers in 11 digits like this:
01205468889 - 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?
CodePudding user response：
You can try this; but result becomes a text, not a number if It satisfies the condition:
=IF(LEN(B2) = 10,"0"&B2,B2)
CodePudding user response：
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.