Reformat phone to numbers
 

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

Reformat phone to numbers
 

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

Reformat phone to numbers
 

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

Reformat phone to numbers
 

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