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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
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
36,050
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,665
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
36,050
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,700
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,090,309
Messages
5,413,722
Members
403,499
Latest member
Kaku2000

This Week's Hot Topics

Top