Can I format telephone numbers?

S

SouthAfricanStan

I have somehow messed up my tel numbers.. idiot!
Previously 011 740 1342, 082 443 0707 and 09 64 456 8799 in different cells.
Now 117401342, 824430707 and 9644568799. Cells are formatted as "numbers, 0
decimals"
Lost the preceding zeros and the spaces. Formatting as "text" does not help.
Other than doing each cell manually (I have over 200 names), is there a way
to get back to what I had before.
The file has been saved - I cannot "undo"...clot!
 
R

Ragdyer

Your problem is that you don't have the same format for all of your numbers.
You have 10 and 11 digit configurations.
How can you differentiate between them?

If they were all 10 digits with a 3-3-4 configuration, you could custom
format your cells to:

000 000 0000

Which would take care of replacing the missing leading 0's.

Don't know what to suggest for telling the 11 digit numbers apart from the
10 digit ones.
 
N

Naveen

Assuming your data of new messed numbers in ColumnA and starts from A1, and
goes down like A2, A3, ...

Try this ...

Paste following function in B1 and copy it to the following cells i.e., B2,
B3, ...

=IF(LEN(A1)<=9,("0"&LEFT(RIGHT(A1,9),2)),("0"&MID(A1,1,LEN(A1)-9))&"
"&(LEFT(RIGHT(A1,9),2)))&" "&LEFT(RIGHT(A1,7),3)&" "&RIGHT(A1,4)


*** Please do rate ***
 
S

SouthAfricanStan

Great! Thank You!
Naveen said:
Assuming your data of new messed numbers in ColumnA and starts from A1,
and
goes down like A2, A3, ...

Try this ...

Paste following function in B1 and copy it to the following cells i.e.,
B2,
B3, ...

=IF(LEN(A1)<=9,("0"&LEFT(RIGHT(A1,9),2)),("0"&MID(A1,1,LEN(A1)-9))&"
"&(LEFT(RIGHT(A1,9),2)))&" "&LEFT(RIGHT(A1,7),3)&" "&RIGHT(A1,4)


*** Please do rate ***
Click to expand...
 
Top