| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to reformat numbers | Excel Discussion (Misc queries) | |||
| Reformat numbers | Excel Discussion (Misc queries) | |||
| Reformat column of telephone numbers | Excel Discussion (Misc queries) | |||
| How can I cross reference phone numbers with existing phone numbe. | Excel Discussion (Misc queries) | |||
| reformat numbers | Excel Programming | |||
Linear Mode
