replace parentheses and dashes from list of #s

rn119

New Member
Joined
Feb 27, 2013
Messages
49
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Code:
Sub rn()
  With Range("D:E")
    .Replace " ", ""
    .Replace "(", ""
    .Replace ")", ""
    .Replace "-", ""
  End With
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,811
Office Version
365
Platform
Windows
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
Joined
Apr 18, 2011
Messages
35,817
Office Version
2010
Platform
Windows
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
Joined
Jun 12, 2014
Messages
38,811
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,443
Office Version
365
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,825
Messages
5,446,721
Members
405,414
Latest member
wayne_p

This Week's Hot Topics

Top