How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.

I just did a format cells....special...phone number and it came out the way
you wanted when i entered data. Is your problem that you physically have the
dashes inbetween the numbers thus making them text?

You could make another column and copy this formula into it. (assuming your
phone numbers start in A1)

="("&LEFT(A1,3)&") "&RIGHT(A1,8)

"JSM123456" wrote:

How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.

Try this:

format a column as Special==(Locale) US=Phone Number

and then put the following in column and copy down:

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

A1=123-456-7890 etc

Copy/paste special=values when complete

"JSM123456" wrote:

How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.

I have the dashes physically in between. Your formula is almost perfect, is
there a way to add the dash before the last four digits? Your help is
appreciated.

"tim m" wrote:

I just did a format cells....special...phone number and it came out the way
you wanted when i entered data. Is your problem that you physically have the
dashes inbetween the numbers thus making them text?

You could make another column and copy this formula into it. (assuming your
phone numbers start in A1)

="("&LEFT(A1,3)&") "&RIGHT(A1,8)

"JSM123456" wrote:

How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.

Hmph the dash should still be there if the dash is physically there, at least
it was on my test.

="("&LEFT(A1,3)&") "&RIGHT(A1,8)

It should work as follows:
It puts a ( to start
It takes the 3 left most characters in the cell
It puts a ) and a space
It takes the 8 right most characters in the cell (this should be ###-####
and thus already include the dash)


"JSM123456" wrote:

I have the dashes physically in between. Your formula is almost perfect, is
there a way to add the dash before the last four digits? Your help is
appreciated.

"tim m" wrote:

I just did a format cells....special...phone number and it came out the way
you wanted when i entered data. Is your problem that you physically have the
dashes inbetween the numbers thus making them text?

You could make another column and copy this formula into it. (assuming your
phone numbers start in A1)
="("&LEFT(A1,3)&") "&RIGHT(A1,8)


"JSM123456" wrote:

How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.

works like a champ. Thnaks for the help.

"tim m" wrote:

Hmph the dash should still be there if the dash is physically there, at least
it was on my test.

="("&LEFT(A1,3)&") "&RIGHT(A1,8)

It should work as follows:
It puts a ( to start
It takes the 3 left most characters in the cell
It puts a ) and a space
It takes the 8 right most characters in the cell (this should be ###-####
and thus already include the dash)


"JSM123456" wrote:

I have the dashes physically in between. Your formula is almost perfect, is
there a way to add the dash before the last four digits? Your help is
appreciated.

"tim m" wrote:

I just did a format cells....special...phone number and it came out the way
you wanted when i entered data. Is your problem that you physically have the
dashes inbetween the numbers thus making them text?

You could make another column and copy this formula into it. (assuming your
phone numbers start in A1)
="("&LEFT(A1,3)&") "&RIGHT(A1,8)


"JSM123456" wrote:

How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.

the problem is that numbers with dashes are considered text. No custom
number format will work unless you remove the dashes. You can do this by
using Find and Replace putting a dash in Find and nothing in replace. Then
click on replace all. Then the custom number format should apply.

"JSM123456" wrote:

How do you convert a phone number in this format xxx-xxx-xxxx to (###)
###-####? I've tried format, cells, custom, (###) ###-#### and "(###)
###-####" but neither worked. I can convert to ########## if needed. I
appreciate your help.