Converting Phone Numbers
 

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.

Converting Phone Numbers
 

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


--
Regards,

Peo Sjoblom



"don" wrote in message
...
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.

Converting Phone Numbers
 

Oops! Should have tested it

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

for text


--
Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
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


--
Regards,

Peo Sjoblom



"don" wrote in message
...
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.

Converting Phone Numbers
 

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" wrote:

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


--
Regards,

Peo Sjoblom



"don" wrote in message
...
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.

Converting Phone Numbers
 

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
datatext to columns and click finish to remove the apostrophe

finally go into formatcellsnumberspecial and select US phone format


--
Regards,

Peo Sjoblom




"don" wrote in message
...
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" wrote:

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


--
Regards,

Peo Sjoblom



"don" wrote in message
...
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.