2014-01-09, 10:36 #1
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
Add parens to first 3 numbers of phone number
Hello - I have an excel file with a column of phone numbers. They are formatted 000-000-0000. I need them to have parens around the area code (000) 000-0000. I haven't found a way to do this by using a special formatting so I think I need to write a macro to iterate through the cells one by one. There are 900 rows, the phone numbers are in column J. Can anyone help me with this macro? Thank you for the assistance.
Last edited by RetiredGeek; 2014-01-09 at 10:50.
Subscribe to our Windows Secrets Newsletter - It's Free!
+ Get this BONUS — free!
2014-01-09, 11:11 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 756 Times in 692 Posts
You can do this with formatting but you have to eliminate the dashes first.
Place this code in a module.
Option Explicit Sub RemoveDashes() 'Note: Place Excel cursor in the column containing the ' phone numbers before calling. Dim lLastRow As Long Dim lCntr As Long Dim zPN As String Dim lPNCol As Long lPNCol = ActiveCell.Column lLastRow = Cells(Rows.Count, lPNCol).End(xlUp).Row For lCntr = 1 To lLastRow zPN = Cells(lLastRow, lPNCol) zPN = Replace(zPN, "-", "") Cells(lLastRow, lPNCol).Formula = "=" & zPN Next lCntr End Sub
Select the RemoveDashes line and click Run.
Now format the column like this.