reformat numbers

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

Guest

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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to select next and rename 1
Adding rows to create list from DDE data 2
Complicated Sort-Compare-Delete Question 4
Running a routine upon opening the file 2
Probably missing something simple in loop macro...Please help 3
help with excel macro 2
Find and replace the 1st occurrence for many strings 2
Variable not defined for "number of trials" ? 3

Back
Top