Converting Phone Numbers

[Guest]

How do you convert phone numbers in the following format '000/000-0000 to the
excel format of (000)000-0000 without retyping each cell. I change the cells
format to phone numbers with no change in the number.

 

[Peo Sjoblom]

If you want text

=TEXT(SUBSTITUTE(A1,"/","-"),"(000)-000-0000")

if you want number just replace (ctrl + H) and replace / with -
then use the built in formatting for US phone numbers


note that in my example your phone number was in A1, adapt accordingly

 

[Peo Sjoblom]

Oops! Should have tested it

=TEXT((SUBSTITUTE(SUBSTITUTE(A1,"/","-"),"-","")),"(000) 000-0000")

for text

 

[Guest]

The control + H worked for removing the /.
How do I remove the ' (apostrophe) at the begining of the string of numbers.
Until that is removed, I am not able to reformat.
My knowledge of your =TEXT formula requires more direction. It is above my
knowledge level.

 

[Peo Sjoblom]

First of all, replace all the - with nothing (ctrl + H and put - in the find
what box and leave replace with box empty), then select the column, do
data>text to columns and click finish to remove the apostrophe

finally go into format>cells>number>special and select US phone format


--
Regards,

Peo Sjoblom