Results 1 to 8 of 8

Remove Brackets from Telephone Numbers

This is a discussion on Remove Brackets from Telephone Numbers within the Excel Questions forums, part of the Question Forums category; I have a column where the telephone numbers were entered with brackets and a dash ie (902) 555-1234 The column ...

  1. #1
    New Member
    Join Date
    May 2003
    Posts
    40

    Default Remove Brackets from Telephone Numbers

    I have a column where the telephone numbers were entered with brackets and a dash
    ie (902) 555-1234
    The column is in Text format.
    I need a macro that will strip away the brackets and dashes and leave the number only for the entire column (over 3500 rows).

    ie (902) 555-1234 to 9025551234

    Any help is greatly appreciated.

    Thanks.
    ambra19

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Remove Brackets from Telephone Numbers

    assuming number in A1 this formula in A2 will return stripped number

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

    If you want VBA let us know.
    Does my a$$ look big in this picture ?

  3. #3
    New Member
    Join Date
    May 2003
    Posts
    40

    Default Re: Remove Brackets from Telephone Numbers

    Thank you very much. It did the trick!
    regards,
    ambra19

  4. #4
    New Member
    Join Date
    Nov 2011
    Posts
    46

    Default Re: Remove Brackets from Telephone Numbers

    I am dealing with the same thing on a regular basis for list uploads and would love a macro to clean these lists.

    Thanks!

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    46

    Default Re: Remove Brackets from Telephone Numbers

    I am dealing with the same thing on a regular basis for list uploads and would love a macro to clean these lists.

    How would I update this to also eliminate any lead "1","1 ","1-"

    Thanks!

  6. #6
    New Member
    Join Date
    Nov 2011
    Posts
    46

    Default Re: Remove Brackets from Telephone Numbers

    I figured out the rest of the substitute formula.
    thx

  7. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,669

    Default Re: Remove Brackets from Telephone Numbers

    You shouldn't need SUBSTITUTE after that, just take the right 10 characters:

    =RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),")",""),"(","")," ",""),10)
    Office 2007/2010

    Be responsible for the questions you post. If you don't reply to answered questions, be it just a simple "yes, that worked," or even "thanks," the time someone took to help you goes unrewarded.

  8. #8
    Xx7
    Xx7 is offline
    Board Regular
    Join Date
    Jan 2011
    Posts
    126

    Default Re: Remove Brackets from Telephone Numbers

    Nice... very logical answer!
    - Excel 2010, Windows 7

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com