Add parens to first 3 numbers of phone number

Topic

New Reply

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.

Reply | Quote

Replies

JHA,

You can do this with formatting but you have to eliminate the dashes first.

Place this code in a module.

Code:

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

Now place the excel cursor in the column containing your phone numbers and press the Alt+F8.
Select the RemoveDashes line and click Run.
Now format the column like this.
35987-ExcelPhoneNoFmt
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote