Formatting phone numbers - vba

Question

I have large data set - 10 000 records with the phone numbers:

Phone_N
5656666666
6676767677
6767677777
5555555555
5555567888
6666777777

I need to format it like:

Phone_N
(565) 666-6666
(222) 767-3333
(676) 777-7777

etc....

My data already have no spaces, "-", "/" or any other characters.

It just needed to be formatted in a proper phone format.

It would be very helpful if you could point me the right direction to start.

Answer 1

This can be done in VBA. You could use the following to accomplish this.

Dim rs As DAO.Recordset, TempN As String
Set rs = CurrentDb.OpenRecordset("SELECT Phone_N FROM MyTableName", dbOpenDynaset)

If (rs.RecordCount <> 0) Then
  rs.MoveFirst
  Do While rs.EOF <> True
    TempN = rs.Fields("[Phone_N]").value
    rs.Edit
    rs.Fields("[Phone_N]").value = "(" & Left(TempN, 3) & ") " & _
                                   Left(Right(TempN, 7), 3) & "-" & _
                                   Right(TempN, 4)
    rs.Update
    rs.MoveNext
  Loop
End If