replace parentheses and dashes from list of #s

[rn119]

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?

 

[shg]

Sub rn()
  With Range("D:E")
    .Replace " ", ""
    .Replace "(", ""
    .Replace ")", ""
    .Replace "-", ""
  End With
End Sub

 

[Fluff]

How about

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

 

[rn119]

That was awfully fast shg! Thank you!!

 

[Rick Rothstein]

Fluff gave you a solution also. And, just to round things out, here is yet one more solution for you...

[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]

 

[rn119]

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

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

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

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