How do I format phone numbers in excel?

Discussion in 'Microsoft Excel Misc' started by Guest, Aug 23, 2006.

  1. Guest

    Guest Guest

    I have an excel file with a column for phone numbers. Some are (xxx)
    xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    do I need to do?
     
    Guest, Aug 23, 2006
    #1
    1. Advertisements

  2. If those () and -'s are really part of the cell (not formatting choices),

    Then select the column
    and do 3 edit|replaces

    edit|replace
    what: (
    with: (leave blank)
    replace all

    edit|replace
    what: )
    with: (leave blank)
    replace all

    edit|replace
    what: -
    with: (leave blank)
    replace all

    Now all your entries should be real numbers and your format|cells|number tab
    choices should work ok.


    CBickley wrote:
    >
    > I have an excel file with a column for phone numbers. Some are (xxx)
    > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > do I need to do?


    --

    Dave Peterson
     
    Dave Peterson, Aug 23, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    If 'phone number in column A, then put this in B and copy down:

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

    HTH

    "CBickley" wrote:

    > I have an excel file with a column for phone numbers. Some are (xxx)
    > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > do I need to do?
     
    Guest, Aug 23, 2006
    #3
  4. Guest

    kfogle Guest

    Format Cells > Choose "Custom" Category > In the "Type:" field on the
    right type in "000-000-0000"
     
    kfogle, Aug 23, 2006
    #4
  5. Guest

    Gord Dibben Guest

    Edit>Replace

    what: (
    with: nothing
    replace all

    Same for )


    Gord Dibben MS Excel MVP

    On Wed, 23 Aug 2006 10:44:01 -0700, CBickley
    <> wrote:

    >I have an excel file with a column for phone numbers. Some are (xxx)
    >xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    >the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    >do I need to do?
     
    Gord Dibben, Aug 23, 2006
    #5
  6. Guest

    Guest Guest

    Thanks, this worked. Now I have a space in the middle of the numbers that I
    need to delete (xxx xxxxxxx). How do I do this?

    "Dave Peterson" wrote:

    > If those () and -'s are really part of the cell (not formatting choices),
    >
    > Then select the column
    > and do 3 edit|replaces
    >
    > edit|replace
    > what: (
    > with: (leave blank)
    > replace all
    >
    > edit|replace
    > what: )
    > with: (leave blank)
    > replace all
    >
    > edit|replace
    > what: -
    > with: (leave blank)
    > replace all
    >
    > Now all your entries should be real numbers and your format|cells|number tab
    > choices should work ok.
    >
    >
    > CBickley wrote:
    > >
    > > I have an excel file with a column for phone numbers. Some are (xxx)
    > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > > do I need to do?

    >
    > --
    >
    > Dave Peterson
    >
     
    Guest, Aug 23, 2006
    #6
  7. Add one more edit|replace

    Edit|Replace
    what: (spacebar)
    with: (leave blank)
    replace all

    CBickley wrote:
    >
    > Thanks, this worked. Now I have a space in the middle of the numbers that I
    > need to delete (xxx xxxxxxx). How do I do this?
    >
    > "Dave Peterson" wrote:
    >
    > > If those () and -'s are really part of the cell (not formatting choices),
    > >
    > > Then select the column
    > > and do 3 edit|replaces
    > >
    > > edit|replace
    > > what: (
    > > with: (leave blank)
    > > replace all
    > >
    > > edit|replace
    > > what: )
    > > with: (leave blank)
    > > replace all
    > >
    > > edit|replace
    > > what: -
    > > with: (leave blank)
    > > replace all
    > >
    > > Now all your entries should be real numbers and your format|cells|number tab
    > > choices should work ok.
    > >
    > >
    > > CBickley wrote:
    > > >
    > > > I have an excel file with a column for phone numbers. Some are (xxx)
    > > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > > > do I need to do?

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson
     
    Dave Peterson, Aug 23, 2006
    #7
  8. Guest

    Gord Dibben Guest

    I'm not sure why OP needs any more than two edit>replaces to get rid of the ()

    Some data looks like (xxx)xxx-xxxx

    Wants it to look like xxx-xxx-xxxx

    Edit>Replace

    what: (
    with: nothing

    Edit>Replace

    what: )
    with: - My first post was replace with nothing which was incorrect.

    returns xxx-xxx-xxxx

    I guess I don't understand why the need to trun the phone number into a number
    just to turn around and custom format as

    xxx-xxx-xxxx


    Gord

    On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <>
    wrote:

    >Add one more edit|replace
    >
    >Edit|Replace
    >what: (spacebar)
    >with: (leave blank)
    >replace all
    >
    >CBickley wrote:
    >>
    >> Thanks, this worked. Now I have a space in the middle of the numbers that I
    >> need to delete (xxx xxxxxxx). How do I do this?
    >>
    >> "Dave Peterson" wrote:
    >>
    >> > If those () and -'s are really part of the cell (not formatting choices),
    >> >
    >> > Then select the column
    >> > and do 3 edit|replaces
    >> >
    >> > edit|replace
    >> > what: (
    >> > with: (leave blank)
    >> > replace all
    >> >
    >> > edit|replace
    >> > what: )
    >> > with: (leave blank)
    >> > replace all
    >> >
    >> > edit|replace
    >> > what: -
    >> > with: (leave blank)
    >> > replace all
    >> >
    >> > Now all your entries should be real numbers and your format|cells|number tab
    >> > choices should work ok.
    >> >
    >> >
    >> > CBickley wrote:
    >> > >
    >> > > I have an excel file with a column for phone numbers. Some are (xxx)
    >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    >> > > do I need to do?
    >> >
    >> > --
    >> >
    >> > Dave Peterson
    >> >


    Gord Dibben MS Excel MVP
     
    Gord Dibben, Aug 23, 2006
    #8
  9. I think that there was an unfortunate line break in the middle of his examples:

    I have an excel file with a column for phone numbers. Some are (xxx)
    xxx-xxxx and others are xxx-xxx-xxxx.

    I think the first example was really:
    (xxx) xxx-xxxx
    So there would have been an extra space in that string.

    And as a personal choice, I'd want all my data the same format--either all text
    or all numbers (nicely formatted).

    I'd convert any text strings xxx-xxx-xxxx to its number and then use
    format|cells to give the whole range a nice consistent look.


    Gord Dibben wrote:
    >
    > I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
    >
    > Some data looks like (xxx)xxx-xxxx
    >
    > Wants it to look like xxx-xxx-xxxx
    >
    > Edit>Replace
    >
    > what: (
    > with: nothing
    >
    > Edit>Replace
    >
    > what: )
    > with: - My first post was replace with nothing which was incorrect.
    >
    > returns xxx-xxx-xxxx
    >
    > I guess I don't understand why the need to trun the phone number into a number
    > just to turn around and custom format as
    >
    > xxx-xxx-xxxx
    >
    > Gord
    >
    > On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <>
    > wrote:
    >
    > >Add one more edit|replace
    > >
    > >Edit|Replace
    > >what: (spacebar)
    > >with: (leave blank)
    > >replace all
    > >
    > >CBickley wrote:
    > >>
    > >> Thanks, this worked. Now I have a space in the middle of the numbers that I
    > >> need to delete (xxx xxxxxxx). How do I do this?
    > >>
    > >> "Dave Peterson" wrote:
    > >>
    > >> > If those () and -'s are really part of the cell (not formatting choices),
    > >> >
    > >> > Then select the column
    > >> > and do 3 edit|replaces
    > >> >
    > >> > edit|replace
    > >> > what: (
    > >> > with: (leave blank)
    > >> > replace all
    > >> >
    > >> > edit|replace
    > >> > what: )
    > >> > with: (leave blank)
    > >> > replace all
    > >> >
    > >> > edit|replace
    > >> > what: -
    > >> > with: (leave blank)
    > >> > replace all
    > >> >
    > >> > Now all your entries should be real numbers and your format|cells|number tab
    > >> > choices should work ok.
    > >> >
    > >> >
    > >> > CBickley wrote:
    > >> > >
    > >> > > I have an excel file with a column for phone numbers. Some are (xxx)
    > >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > >> > > do I need to do?
    > >> >
    > >> > --
    > >> >
    > >> > Dave Peterson
    > >> >

    >
    > Gord Dibben MS Excel MVP


    --

    Dave Peterson
     
    Dave Peterson, Aug 23, 2006
    #9
  10. And by having the format of the field consistent, I can use =vlookup() and
    =match() without having to worry about if the data is text or numeric.

    Dave Peterson wrote:
    >
    > I think that there was an unfortunate line break in the middle of his examples:
    >
    > I have an excel file with a column for phone numbers. Some are (xxx)
    > xxx-xxxx and others are xxx-xxx-xxxx.
    >
    > I think the first example was really:
    > (xxx) xxx-xxxx
    > So there would have been an extra space in that string.
    >
    > And as a personal choice, I'd want all my data the same format--either all text
    > or all numbers (nicely formatted).
    >
    > I'd convert any text strings xxx-xxx-xxxx to its number and then use
    > format|cells to give the whole range a nice consistent look.
    >
    > Gord Dibben wrote:
    > >
    > > I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
    > >
    > > Some data looks like (xxx)xxx-xxxx
    > >
    > > Wants it to look like xxx-xxx-xxxx
    > >
    > > Edit>Replace
    > >
    > > what: (
    > > with: nothing
    > >
    > > Edit>Replace
    > >
    > > what: )
    > > with: - My first post was replace with nothing which was incorrect.
    > >
    > > returns xxx-xxx-xxxx
    > >
    > > I guess I don't understand why the need to trun the phone number into a number
    > > just to turn around and custom format as
    > >
    > > xxx-xxx-xxxx
    > >
    > > Gord
    > >
    > > On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <>
    > > wrote:
    > >
    > > >Add one more edit|replace
    > > >
    > > >Edit|Replace
    > > >what: (spacebar)
    > > >with: (leave blank)
    > > >replace all
    > > >
    > > >CBickley wrote:
    > > >>
    > > >> Thanks, this worked. Now I have a space in the middle of the numbers that I
    > > >> need to delete (xxx xxxxxxx). How do I do this?
    > > >>
    > > >> "Dave Peterson" wrote:
    > > >>
    > > >> > If those () and -'s are really part of the cell (not formatting choices),
    > > >> >
    > > >> > Then select the column
    > > >> > and do 3 edit|replaces
    > > >> >
    > > >> > edit|replace
    > > >> > what: (
    > > >> > with: (leave blank)
    > > >> > replace all
    > > >> >
    > > >> > edit|replace
    > > >> > what: )
    > > >> > with: (leave blank)
    > > >> > replace all
    > > >> >
    > > >> > edit|replace
    > > >> > what: -
    > > >> > with: (leave blank)
    > > >> > replace all
    > > >> >
    > > >> > Now all your entries should be real numbers and your format|cells|number tab
    > > >> > choices should work ok.
    > > >> >
    > > >> >
    > > >> > CBickley wrote:
    > > >> > >
    > > >> > > I have an excel file with a column for phone numbers. Some are (xxx)
    > > >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > > >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > > >> > > do I need to do?
    > > >> >
    > > >> > --
    > > >> >
    > > >> > Dave Peterson
    > > >> >

    > >
    > > Gord Dibben MS Excel MVP

    >
    > --
    >
    > Dave Peterson


    --

    Dave Peterson
     
    Dave Peterson, Aug 23, 2006
    #10
  11. Guest

    Gord Dibben Guest

    Not seeing the line break, but sure enough could be there.

    Now I'm all warm and fuzzy again and can quit stressing myself over sh.. like
    this.




    On Wed, 23 Aug 2006 16:24:44 -0500, Dave Peterson <>
    wrote:

    >I think that there was an unfortunate line break in the middle of his examples:
    >
    >I have an excel file with a column for phone numbers. Some are (xxx)
    >xxx-xxxx and others are xxx-xxx-xxxx.
    >
    >I think the first example was really:
    >(xxx) xxx-xxxx
    >So there would have been an extra space in that string.
    >
    >And as a personal choice, I'd want all my data the same format--either all text
    >or all numbers (nicely formatted).
    >
    >I'd convert any text strings xxx-xxx-xxxx to its number and then use
    >format|cells to give the whole range a nice consistent look.
    >
    >
    >Gord Dibben wrote:
    >>
    >> I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
    >>
    >> Some data looks like (xxx)xxx-xxxx
    >>
    >> Wants it to look like xxx-xxx-xxxx
    >>
    >> Edit>Replace
    >>
    >> what: (
    >> with: nothing
    >>
    >> Edit>Replace
    >>
    >> what: )
    >> with: - My first post was replace with nothing which was incorrect.
    >>
    >> returns xxx-xxx-xxxx
    >>
    >> I guess I don't understand why the need to trun the phone number into a number
    >> just to turn around and custom format as
    >>
    >> xxx-xxx-xxxx
    >>
    >> Gord
    >>
    >> On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <>
    >> wrote:
    >>
    >> >Add one more edit|replace
    >> >
    >> >Edit|Replace
    >> >what: (spacebar)
    >> >with: (leave blank)
    >> >replace all
    >> >
    >> >CBickley wrote:
    >> >>
    >> >> Thanks, this worked. Now I have a space in the middle of the numbers that I
    >> >> need to delete (xxx xxxxxxx). How do I do this?
    >> >>
    >> >> "Dave Peterson" wrote:
    >> >>
    >> >> > If those () and -'s are really part of the cell (not formatting choices),
    >> >> >
    >> >> > Then select the column
    >> >> > and do 3 edit|replaces
    >> >> >
    >> >> > edit|replace
    >> >> > what: (
    >> >> > with: (leave blank)
    >> >> > replace all
    >> >> >
    >> >> > edit|replace
    >> >> > what: )
    >> >> > with: (leave blank)
    >> >> > replace all
    >> >> >
    >> >> > edit|replace
    >> >> > what: -
    >> >> > with: (leave blank)
    >> >> > replace all
    >> >> >
    >> >> > Now all your entries should be real numbers and your format|cells|number tab
    >> >> > choices should work ok.
    >> >> >
    >> >> >
    >> >> > CBickley wrote:
    >> >> > >
    >> >> > > I have an excel file with a column for phone numbers. Some are (xxx)
    >> >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    >> >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    >> >> > > do I need to do?
    >> >> >
    >> >> > --
    >> >> >
    >> >> > Dave Peterson
    >> >> >

    >>
    >> Gord Dibben MS Excel MVP


    Gord Dibben MS Excel MVP
     
    Gord Dibben, Aug 23, 2006
    #11
  12. When life gets too tense, reduce it to one fifth!

    (Sounds better than reading)

    Gord Dibben wrote:
    >
    > Not seeing the line break, but sure enough could be there.
    >
    > Now I'm all warm and fuzzy again and can quit stressing myself over sh.. like
    > this.
    >
    > On Wed, 23 Aug 2006 16:24:44 -0500, Dave Peterson <>
    > wrote:
    >
    > >I think that there was an unfortunate line break in the middle of his examples:
    > >
    > >I have an excel file with a column for phone numbers. Some are (xxx)
    > >xxx-xxxx and others are xxx-xxx-xxxx.
    > >
    > >I think the first example was really:
    > >(xxx) xxx-xxxx
    > >So there would have been an extra space in that string.
    > >
    > >And as a personal choice, I'd want all my data the same format--either all text
    > >or all numbers (nicely formatted).
    > >
    > >I'd convert any text strings xxx-xxx-xxxx to its number and then use
    > >format|cells to give the whole range a nice consistent look.
    > >
    > >
    > >Gord Dibben wrote:
    > >>
    > >> I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
    > >>
    > >> Some data looks like (xxx)xxx-xxxx
    > >>
    > >> Wants it to look like xxx-xxx-xxxx
    > >>
    > >> Edit>Replace
    > >>
    > >> what: (
    > >> with: nothing
    > >>
    > >> Edit>Replace
    > >>
    > >> what: )
    > >> with: - My first post was replace with nothing which was incorrect.
    > >>
    > >> returns xxx-xxx-xxxx
    > >>
    > >> I guess I don't understand why the need to trun the phone number into a number
    > >> just to turn around and custom format as
    > >>
    > >> xxx-xxx-xxxx
    > >>
    > >> Gord
    > >>
    > >> On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <>
    > >> wrote:
    > >>
    > >> >Add one more edit|replace
    > >> >
    > >> >Edit|Replace
    > >> >what: (spacebar)
    > >> >with: (leave blank)
    > >> >replace all
    > >> >
    > >> >CBickley wrote:
    > >> >>
    > >> >> Thanks, this worked. Now I have a space in the middle of the numbers that I
    > >> >> need to delete (xxx xxxxxxx). How do I do this?
    > >> >>
    > >> >> "Dave Peterson" wrote:
    > >> >>
    > >> >> > If those () and -'s are really part of the cell (not formatting choices),
    > >> >> >
    > >> >> > Then select the column
    > >> >> > and do 3 edit|replaces
    > >> >> >
    > >> >> > edit|replace
    > >> >> > what: (
    > >> >> > with: (leave blank)
    > >> >> > replace all
    > >> >> >
    > >> >> > edit|replace
    > >> >> > what: )
    > >> >> > with: (leave blank)
    > >> >> > replace all
    > >> >> >
    > >> >> > edit|replace
    > >> >> > what: -
    > >> >> > with: (leave blank)
    > >> >> > replace all
    > >> >> >
    > >> >> > Now all your entries should be real numbers and your format|cells|number tab
    > >> >> > choices should work ok.
    > >> >> >
    > >> >> >
    > >> >> > CBickley wrote:
    > >> >> > >
    > >> >> > > I have an excel file with a column for phone numbers. Some are (xxx)
    > >> >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
    > >> >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
    > >> >> > > do I need to do?
    > >> >> >
    > >> >> > --
    > >> >> >
    > >> >> > Dave Peterson
    > >> >> >
    > >>
    > >> Gord Dibben MS Excel MVP

    >
    > Gord Dibben MS Excel MVP


    --

    Dave Peterson
     
    Dave Peterson, Aug 23, 2006
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    287
    Guest
    Feb 11, 2005
  2. LACA

    Format sequence for phone numbers?

    LACA, Jun 1, 2006, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    167
  3. Guest
    Replies:
    2
    Views:
    195
    Gord Dibben
    Jun 23, 2006
  4. Guest
    Replies:
    2
    Views:
    182
    Gord Dibben
    Jun 23, 2006
  5. Access Joe

    Format phone numbers to look the same

    Access Joe, Apr 30, 2008, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    160
    Access Joe
    Apr 30, 2008
Loading...

Share This Page