replace parentheses and dashes from list of #s

rn119

New Member
Joined
Feb 27, 2013
Messages
47
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
Joined
May 7, 2008
Messages
21,674
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
30,113
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,393
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
30,113
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,683
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:

Forum statistics

Threads
1,077,639
Messages
5,335,396
Members
399,015
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top