reformat numbers

[waterman]

i have a list of phone numbers that are givin with (###)###-####, what i would like to do is get rid of the "(" ")" "-
so far i have

sub changeove
Sheets("tracking").Selec
Range("w4:w100").Selec
ActiveCell.Formula = Replace(ActiveCell.Value, "-", ""
ActiveCell.Formula = Replace(ActiveCell.Value, "(", ""
ActiveCell.Formula = Replace(ActiveCell.Value, ")", ""
Selection.Cop
Range("x4").Selec
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
:=False, Transpose:=Fals

end su

this only changes the first one though. im not sure on how to get rid of the symbols on all the number

 

[Edwin Tam]

Try to use the macro below

Sub changeover(
With Worksheets("tracking"
With Range("w4:w100"
.Replace What:="(", Replacement:="
.Replace What:=")", Replacement:="
.Replace What:="-", Replacement:="
.Cop
End Wit
.Range("x4").PasteSpecial Paste:=xlPasteValue
Application.CutCopyMode = Fals
End Wit
End Su

Regards
Edwin Ta
(e-mail address removed)
http://www.vonixx.co

----- waterman wrote: ----

i have a list of phone numbers that are givin with (###)###-####, what i would like to do is get rid of the "(" ")" "-
so far i have

sub changeove
Sheets("tracking").Selec
Range("w4:w100").Selec
ActiveCell.Formula = Replace(ActiveCell.Value, "-", ""
ActiveCell.Formula = Replace(ActiveCell.Value, "(", ""
ActiveCell.Formula = Replace(ActiveCell.Value, ")", ""
Selection.Cop
Range("x4").Selec
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
:=False, Transpose:=Fals

end su

this only changes the first one though. im not sure on how to get rid of the symbols on all the number