Results 1 to 8 of 8

Thread: replace parentheses and dashes from list of #s
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default replace parentheses and dashes from list of #s

    I have a list of numbers in static columns (let's stay columns D and E have the home and business phone numbers) that need to have all dashes, spaces and parentheses removed.

    Raw: (213) 345-4567

    Desired: 2133454567

    I know I can do this via formulas and such but I would prefer to do this via VBA. Any assistance with a macro?

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    Code:
    Sub rn()
      With Range("D:E")
        .Replace " ", ""
        .Replace "(", ""
        .Replace ")", ""
        .Replace "-", ""
      End With
    End Sub
    Last edited by shg; Sep 1st, 2019 at 03:42 PM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,957
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    How about
    Code:
    Sub rn119()
       Dim Ary As Variant
       Dim i As Long
       Ary = Array("(", ")", " ", "-")
       For i = 0 To UBound(Ary)
          Range("D:E").Replace Ary(i), "", xlPart, , , , False, False
       Next i
    End Sub
    Last edited by Fluff; Sep 1st, 2019 at 03:44 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Feb 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    That was awfully fast shg! Thank you!!

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,207
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    Fluff gave you a solution also. And, just to round things out, here is yet one more solution for you...
    Code:
    Sub RawPhoneNumbers() Dim Addr As String Addr = Range("D2:E" & Range("D:E").Find("*", , xlValues, , xlRows, xlPrevious).Row).Address Range(Addr) = Evaluate(Replace("IF(@="""","""",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(@,"" "",""""),""("",""""),"")"",""""),""-"",""""))", "@", Addr)) End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Feb 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    Thank you Fluff and Rick. These solutions worked as well!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,957
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,957
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: replace parentheses and dashes from list of #s

    Quote Originally Posted by rn119 View Post
    These solutions worked as well!
    Just a few comments & I don't know if these things are possible with your data but ..

    - If you have any numbers with leading zeros [eg (013) 345-4567] the first 2 codes will lose that leading zero
    - If you have any long numbers, perhaps with leading country dial-out numbers [eg (0011)213-365-5698] & your columns are formatted as General, all 3 codes may visually convert that longer number to a number in scientific notation [eg 1.12134E+11]
    - Same situation as the previous point but columns already formatted as Text, the first 2 codes may still convert to scientific notation.

    As an added option in case any of the above issues could occur for you, here is another code that I believe covers those issues and would also eliminate any other non-digit characters that may turn up in your data eg (0011) 213#365#5699 or [213] 345-4567

    Code:
    Sub NumsOnly()
      Dim a As Variant
      Dim i As Long, j As Long
      
      a = Range("D1:E" & Range("D:E").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Value
      With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        For i = 1 To UBound(a)
          For j = 1 To 2
            a(i, j) = .Replace(a(i, j), "")
          Next j
        Next i
      End With
      With Range("D1:E1").Resize(UBound(a))
        .NumberFormat = "@"
        .Value = a
      End With
    End Sub
    Last edited by Peter_SSs; Sep 1st, 2019 at 06:41 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •