+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Alan
    Guest

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

    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.

  2. #2

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

    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


  3. #3
    Harlan Grove
    Guest

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

    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.


    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?


  4. #4
    Alan
    Guest

    Re: How do I add parenthesis to my phone number list -the area cod

    Craig,
    All numbers are in the same cell like: 999-999-9999. I need all 3,000
    numbers to appear (999) 999-9999.
    Alan

    "[email protected]" wrote:

    > 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
    >
    >


  5. #5
    Alan
    Guest

    Re: How do I add parenthesis to my phone number list -the area cod

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

    > 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.

    >
    > 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?
    >
    >


  6. #6

    Re: How do I add parenthesis to my phone number list -the area cod

    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


  7. #7

    Re: How do I add parenthesis to my phone number list -the area cod

    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)


  8. #8
    Harlan Grove
    Guest

    Re: How do I add parenthesis to my phone number list -the area cod

    [email protected] 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

    ....

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1