telephone number format change in Excel
 

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.

telephone number format change in Excel
 

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,""),S UBSTITUTE(F1," (","-")),")
","-")

Rick

telephone number format change in Excel
 

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


"Sherry" wrote:

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.

telephone number format change in Excel
 

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.

"Rick Rothstein (MVP - VB)" wrote:

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,""),S UBSTITUTE(F1," (","-")),")
","-")

Rick

telephone number format change in Excel
 

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.

"Teethless mama" wrote:

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


"Sherry" wrote:

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.

telephone number format change in Excel
 

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.

"Teethless mama" wrote:

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


"Sherry" wrote:

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.

telephone number format change in Excel
 

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

"Teethless mama" wrote:

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


"Sherry" wrote:

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.

telephone number format change in Excel
 

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

On Dec 14, 1:00 am, Sherry wrote:
How do I make it remove
the "1" at the beginning of the phone number too.



"Teethless mama" wrote:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," (","-"),") ","-"),"(","")

"Sherry" wrote:

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. - Hide quoted text -

- Show quoted text -