Try this

Sub replaceem()
lr=cells(rows.count,"F").end(xlup).row
myarray = Array("-", "(", ")")
For Each i In myarray
Range("f4:f" & lr).Replace i, "", LookAt:=xlPart
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ZikJackson" wrote in message
...
I'm new to VBA in Excel. What code can I use to reformat a phone number
from
this

(123)456-7890

to this?

1234567890

I've learned a lot reading the responses in this forum. Right now I've
recorded two macros to copy/paste the old format numbers to a separate
sheet,
use a formula

ActiveCell.FormulaR1C1 =
"=MID(RC[-1],2,3)&MID(RC[-1],6,3)&MID(RC[-1],10,4)"

to reformat the number, and copy/paste it back to the original sheet, but
I'm *sure* there's an easier way. Here's my current "script kiddie" code:

Sub Macro9aReformatPhone()
'
' Macro9aReformatPhone Macro
' Reformat Phone to 10-digits. Macro recorded 9/24/2008
'

'
Sheets("Sheet1").Select
Range("L1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F1").Select
ActiveCell.FormulaR1C1 =
"=MID(RC[-1],2,3)&MID(RC[-1],6,3)&MID(RC[-1],10,4)"
Range("F1").Select
Selection.AutoFill Destination:=Range(Selection,
ActiveCell.SpecialCells(xlLastCell))
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

End Sub
Sub Macro9bCopyPastePhone()
'
' Macro9bCopyPastePhone Macro
' Copy phone numbers, paste values back to Sheet1. Macro recorded
9/25/2008
'
Sheets("Sheet3").Select
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("L1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks in advance,

Zik