Although one can easily use an input mask to format value, including phone numbers. It became necessary for me to be able to do the same type of formatting but directly through VBA. Below is a simple routine, but hopefully it will help you.

This procedure becomes very useful when you need to deal with phone numbers from varying regions. If you only will ever deal with a preset phone number format then I recommend using the input mask. However, if you will need to accommodate and format all sorts of international phone number formats the procedure is the way to go and you can easily add new format to it by simply adding new Case statement clauses.

'---------------------------------------------------------------------------------------
' Procedure : FormPhone
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Format a string into a formatted phone number
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPhone    : a raw phone number string (ie:5555555)
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' FormPhone("5555555")      -->>    555-5555
' FormPhone("5555555555")   -->>    (555)555-5555
' FormPhone("55555555555")  -->>    5(555)555-5555
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Feb-13             Initial Release
' 2         2010-Jan-26             Expanded code to format 10 and 11 char phone no.
'---------------------------------------------------------------------------------------
Function FormPhone(sPhone As String) As String
On Error GoTo Error_Handler
    Dim sIni        As String
    Dim sArea       As String
    Dim sPNumber    As String
    Dim isPhoneLen  As Integer
    
    'Make sure we actually have a value to process
    If sPhone = "" Or IsNull(sPhone) Then
        sPhone = ""
        Exit Function
    End If
    
    sPhone = Trim(sPhone)    'Remove any leading or trailing white spaces
    isPhoneLen = Len(sPhone) 'Determine the length of the raw tel no.
    Select Case isPhoneLen
        Case 7  '555-5555
            FormPhone = Left(sPhone, 3) & "-" & Right(sPhone, Len(sPhone) - 3)
        Case 10 '(555)555-5555
            sArea = "(" & Left(sPhone, 3) & ") "
            sPNumber = Right(sPhone, Len(sPhone) - 3)
            FormPhone = sArea & Left(sPNumber, 3) & "-" & _
                        Right(sPNumber, Len(sPNumber) - 3)
        Case 11 '5(555)555-5555
            sIni = Left(sPhone, 1)
            sArea = "(" & Left(Right(sPhone, Len(sPhone) - 1), 3) & ") "
            sPNumber = Right(sPhone, Len(sPhone) - 4)
            FormPhone = sIni & sArea & Left(sPNumber, 3) & _
                        "-" & Right(sPNumber, Len(sPNumber) - 3)
        Case Else
            FormPhone = sPhone
    End Select
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & "Error Source: FormPhone" & _
           vbCrLf & "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

 

Updated 2011-04-14

Below you will find another approach to formatting phone numbers.

Function StripAllChars(strString As String) As String
'Return only numeric values from a string
    Dim lngCtr      As Long
    Dim intChar     As Integer

    For lngCtr = 1 To Len(strString)
        intChar = Asc(Mid(strString, lngCtr, 1))
        If intChar >= 48 And intChar <= 57 Then
            StripAllChars = StripAllChars & Chr(intChar)
        End If
    Next lngCtr
End Function

Function FormatPhone(strIn As String) As Variant
On Error Resume Next

    strIn = StripAllChars(strIn)
    
    If InStr(1, strIn, "@") >= 1 Then
        FormatPhone = strIn
        Exit Function
    End If
    
    Select Case Len(strIn & vbNullString)
        Case 0
            FormatPhone = Null
        Case 7
            FormatPhone = Format(strIn, "@@@-@@@@")
        Case 10
            FormatPhone = Format(strIn, "(@@@) @@@-@@@@")
        Case 11
            FormatPhone = Format(strIn, "@ (@@@) @@@-@@@@")
        Case Else
            FormatPhone = strIn
    End Select
End Function

Both methods work. That said, the second one is much easier to keep adding to and probably run faster too since it does not call upon other procedures (Left(), Right(), Mid(), …) in it’s formatting process. Although with today’s computers you couldn’t tell the difference anyways.