Results 1 to 2 of 2
2014-01-09, 10:36 #1
- Join Date
- Jan 2001
- Thanked 1 Time in 1 Post
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.
2014-01-09, 11:11 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,510 Times in 1,371 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.