Formatting Macro Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code reformats phone numbers from "(123) 456-7890" to
"123-456-7890". The macro performs this function perfectly, but the issue is
that it overwrites all strings in the list with the first string in the list.

Example: The macro changes the following list from...
(123) 456-7890
(234) 567-8901
(345) 678-9012
(456) 789-0123
To...
123-456-7890
123-456-7890
123-456-7890
123-456-7890

Here's the code I'm using:

Sub ReplaceParentesesWithDash()
Application.ScreenUpdating = False
Dim cell As Range
Set r = Selection
For Each cell In r
v = cell.Text
v = Replace(v, "(", "")
v = Replace(v, ")", "")
v = Replace(v, " ", "")
v = Replace(v, "-", "")
v = Left(v, 3) & "-" & Mid(v, 4, 3) & "-" & Right(v, 4)
r.Value = v
Next cell
Application.ScreenUpdating = True
End Sub

Thank you.
Exceller
 
Back
Top