Reformat phone to numbers

Z

ZikJackson

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
 
D

Don Guillett

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
 
Z

ZikJackson

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 said:
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
(e-mail address removed)
ZikJackson said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
ZikJackson said:
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 said:
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
(e-mail address removed)
ZikJackson said:
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
Click to expand...
Click to expand...
Click to expand...
 

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

Different Results from the Same Macro 3
Help again!!! 1
variable number of rows. 3
Cleanup this macro please 3
macro to copy rows that contain a cell with a certain number 3
Advanced Filter works in recorder but not while running macro 5
how to use Solver in Excel VBA 1
smart tags 1

Top