How do I add parenthesis to my phone number list -the area code?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel list of phone numbers that I need to add parenthesis around
the area code. Anyone know how to do this? The list is about 3,000 numbers.
 
You could try something like Data -> Text To columns, by fixed length,
choose to seperate out the first three digits - then in another column
have the equation ="(" & A1 & ") " & B1

this assumes that you have the first three in the a column and the
remaining in b, you could also split off the last four at the same time
then have the equestion ="(" & A1 & ") " & B1 & "-" & C1 to have the
number format out to (###) ###-#### then just copy the formula down the
column
 
Alan wrote...
I have an Excel list of phone numbers that I need to add parenthesis around
the area code. Anyone know how to do this? The list is about 3,000 numbers.
Click to expand...

Are these phone numbers stored as numbers or as text, and do you just
need them to appear as (###) ###-#### ? If they're numbers and you just
need them to appear with area codes in parentheses, you could do that
with the custom number format

[>=10000000](#) 000-0000;000-0000

If your phone numbers are stored as text, how are they formatted?
 
Craig,
All numbers are in the same cell like: 999-999-9999. I need all 3,000
numbers to appear (999) 999-9999.
Alan
 
The numbers are store as numbers. Right now they are 999-999-9999 and I need
them to appear as (999) 999-9999. I tried what you recommended but I wasn't
able to pull it off.

Alan

Harlan Grove said:
Alan wrote...
I have an Excel list of phone numbers that I need to add parenthesis around
the area code. Anyone know how to do this? The list is about 3,000 numbers.
Click to expand...

Are these phone numbers stored as numbers or as text, and do you just
need them to appear as (###) ###-#### ? If they're numbers and you just
need them to appear with area codes in parentheses, you could do that
with the custom number format

[>=10000000](#) 000-0000;000-0000

If your phone numbers are stored as text, how are they formatted?
Click to expand...
 
the text to columns would still get what you're looking for, it will
require you to insert some blank columns after the column that the
numbers are in now. try this, insert three blank columns after the
column that the phone numbers are currently in. then data -> text to
columns, choose delimited, then other and put - is as the delimiter
then on the next screen choose general as the column data format, then
click finish. this will split up the three parts of the phone number
into three seperate columns, then in the fourth column, enter the
equation ="(" & A1 ") " & B1 & "-" & C1

this will put the three parts back together into one column, you can
then either hide the extra columns, or copy and paste special values
the column back to the original and delete the additional columns
you've added
 
oh, and to do this you'll want to select all 3000 entries before you do
the text to columns, then once you've put the equation in one row, copy
it down all 3000, that should give you the format (999) 999-9999 (i put
a space in the ") " part to seperate the area code from the rest of the
phone number as that seemed to be the format you were looking for)
 
(e-mail address removed) wrote...
the text to columns would still get what you're looking for, it will
require you to insert some blank columns after the column that the
numbers are in now. try this, insert three blank columns after the
column that the phone numbers are currently in. then data -> text to
columns, choose delimited, then other and put - is as the delimiter
then on the next screen choose general as the column data format, then
click finish. this will split up the three parts of the phone number
into three seperate columns, then in the fourth column, enter the
equation ="(" & A1 ") " & B1 & "-" & C1
Click to expand...
....

Perhaps, but it'd take fewer steps to select all these phone numbers,
run Edit>Replace to replace all dashes with nothing, then apply the
custom number format

[>=10000000](#) 000-0000;000-0000

No additional columns or formulas needed.

For the OP's benefit: if the cells contain phone numbers with dashes
after area code an exchange, then they're stored as text.
 
Back
Top