About Us

Don Guillett

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ZikJackson" wrote in message
...
Works great, thanks Don!

P.S. I changed

Range("f4:f" & lr).Replace i, "", LookAt:=xlPart

to

Range("f1:f" & lr).Replace i, "", LookAt:=xlPart

to include all rows in my list.

I also noticed some of my cells have extra text at the end, so a few came
out

1234567890 CELL or
1234567890 WORK

is there an easy way to truncate these AND format to text (so they don't
come out as scientific notation)?

Zik

"Don Guillett" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to reformat numbers	C.Loner	Excel Discussion (Misc queries)	4	January 23rd 08 12:31 AM
Reformat numbers	EllenM	Excel Discussion (Misc queries)	6	August 8th 07 04:22 PM
Reformat column of telephone numbers	Maree	Excel Discussion (Misc queries)	2	March 1st 06 09:30 PM
How can I cross reference phone numbers with existing phone numbe.	John	Excel Discussion (Misc queries)	1	February 11th 05 04:39 PM
reformat numbers	waterman	Excel Programming	1	April 28th 04 02:41 AM

Menu

About Us