replace parentheses and dashes from list of #s

rn119

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

MrExcel MVP
Code:
Sub rn()
  With Range("D:E")
    .Replace " ", ""
    .Replace "(", ""
    .Replace ")", ""
    .Replace "-", ""
  End With
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
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:

Rick Rothstein

MrExcel MVP
Fluff gave you a solution also. And, just to round things out, here is yet one more solution for you...
Code:
[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]
 

Fluff

MrExcel MVP, Moderator
Glad we could help & thanks for the feedback
 

Peter_SSs

MrExcel MVP, Moderator
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:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top