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 our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-01-09, 11:11 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 760 Times in 695 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.