telephone number format change in Excel

[Sherry]

I have telephone numbers that are formatted as (###) ###-####. I need a
formula to change the format to ###-###-####.
Also need the same for telephone numbers that are formatted as # (###)
###-####. Please help.

 

[Rick Rothstein \(MVP - VB\)]

I have telephone numbers that are formatted as (###) ###-####.

I need a formula to change the format to ###-###-####.
Also need the same for telephone numbers that are formatted
as # (###) ###-####.

Give this formula a try...

=SUBSTITUTE(IF(LEFT(F1,1)="(",REPLACE(F1,1,1,""),SUBSTITUTE(F1," (","-")),")
","-")

Rick

 

[Teethless mama]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," (","-"),") ","-"),"(","")

 

[Sherry]

That was very helpful. I accidentally left out one thing... for telephone
numbers that are formatted as "1 (###) ###-####", the formula needs to remove
the "1"1 at the beginning too. Sorry for the additional trouble. Thanks.

 

[Sherry]

That was very helpful. I accidentally left out one thing... for telephone
numbers that are formatted as "1 (###) ###-####", the formula needs to remove
the "1"1 at the beginning too. Sorry for the additional trouble. Thanks.

 

[Sherry]

That was very helpful. I accidentally left out one thing... for telephone
numbers that are formatted as "1 (###) ###-####", the formula needs to remove
the "1"1 at the beginning too. Sorry for the additional trouble. Thanks.

 

[Sherry]

How do I make it remove
the "1" at the beginning of the phone number too.

 

[Pete_UK]

I hope you have not been waiting since June for an answer !! <bg>

As your "numbers" are really text values, you can use this formula to
remove the 1<space> from the beginning of the phone number (assume it
is in A1):

=RIGHT(A1,LEN(A1)-2)

Hope this helps.

Pete