Parsing a Name and Telephone number string

[Steven Deetz]

I have converted an Excel file into an Access table and now want to break down information in this table into more meaningful fields. One of the fields I want to breakdown is a Emergency Contact text field that lists a name and a telephone number combined. Below is an example of the data in the field.

Emergency Contact
Steve Deetz (507) 455-0105
Steve 455-0105
Steve Deetz 455-0105
Steven Deetz 507-455-0105

I can use the Left, Right, and Mid functions to parse out the information, but due to no uniformity on how the data was entered, the parsed data looks rather ugly. I have done a search of the site for parsing information and looked at a few posts without much luck.

If there is a way to get a count of the number of characters to the very first number, I could then use the Left, Right, and Mid functions to extract both the name and telephone number. Any ideas on how to do this would make my day.

Thanks in advance!

 

[Len Boorman]

I would use Excel facilities to split up the data before importing into Access

Len

 

[mresann]

Part of the problem is that sometimes numbers are not formatted consistantly from record to record for simple parsing functions, especially if they are manually entered in Excel sheets. In addition, phone numbers may be incomplete and you may not be able to format it properly. Finally, sometimes a phone number entered has a "1" in front of it, which also screws up a simple parsing code function.

The assumption is that the phone format follows the American format of either

(534) 555-1212 for long distance and
555-1212 for local

I created this module that will perform the following:

1. Determines if the input phone number has exactly 7 digits (no area code), 10 digits (area code + 7 digit number), or 11 digits ('1' plus area code plus number).

2. Determines if the desired output is a formatted number such as
"(714) 555-1212" or bare number "7145551212"

3. If the digit count doesn't match properly, then the original number with formatting is output.

Place this code in a module, whether it be Excel or Access or any VBA or VB module.

Function fncPhoneNumberFormat(ByVal strPhoneNo As String, _
                              Optional blnBareNumber As Boolean = False) As String

PROC_DECLARATIONS:
   Dim strBareNumber             As String
   Dim strLeftParenthesis        As String
   Dim strRightParenthesis       As String
   Dim strHyphen                 As String
   Dim strNumber                 As String
   Dim strPhoneNumberFormat      As String
   Dim strAreaCode               As String
   Dim strPrefix                 As String
   Dim strSuffix                 As String
   Dim lngCounter                As Long
   
PROC_START:
   On Error GoTo PROC_ERROR
   'better to use constants, but this is good for now
   strLeftParenthesis = "("
   strRightParenthesis = ")"
   strHyphen = "-"
   strNumber = "0123456789"
   
PROC_MAIN:
   'strip phone number
   For lngCounter = 1 To Len(strPhoneNo)
      If InStr(strNumber, Mid(strPhoneNo, lngCounter, 1)) > 0 Then
         strBareNumber = strBareNumber & Mid(strPhoneNo, lngCounter, 1)
      End If
   Next
   
   'If the output required is a bare number, assign to output and exit
   If blnBareNumber Then
      strPhoneNumberFormat = strBareNumber
      GoTo PROC_EXIT
   End If
   
   'phone number will be formatted
   'determine if number is 7-digit (local) or 10-11 digit (long distance)
   If Len(strBareNumber) > 7 Then
      'long distance
      'determine if there is 11 digits; assumption is there is a '1' in the
      'first digit and needs to be eliminated
      If Len(strBareNumber) = 11 Then
         'eliminate first digit
         strBareNumber = Right(strBareNumber, 10)
      End If
      
      If Len(strBareNumber) = 10 Then
         'now create Area code string
         strAreaCode = Left(strBareNumber, 3)
         'create prefix string
         strPrefix = Mid(strBareNumber, 4, 3)
         'create suffix string
         strSuffix = Right(strBareNumber, 4)
         'cocatenate whole string
         strPhoneNumberFormat = strLeftParenthesis & _
                                strAreaCode & _
                                strRightParenthesis & _
                                Space(1) & _
                                strPrefix & _
                                strHyphen & _
                                strSuffix
         GoTo PROC_EXIT
      End If
   Else
      'check for 7 digits
      If Len(strBareNumber) = 7 Then
         'local number, just use prefix, hyphen and suffix
         'create prefix string
         strPrefix = Left(strBareNumber, 3)
         'create suffix string
         strSuffix = Right(strBareNumber, 4)
         'cocatenate whole string
         strPhoneNumberFormat = strPrefix & _
                                strHyphen & _
                                strSuffix
         GoTo PROC_EXIT
      End If
   End If
   
   'If this point is reached, telephone number does not have
   '7, 10, or 11 digits, so pass the original string back
   strPhoneNumberFormat = strPhoneNo
   
PROC_EXIT:
   fncPhoneNumberFormat = strPhoneNumberFormat
   Exit Function

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: fncPhoneNumberFormat" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT

End Function

Incidentally, you can use this function as a template to create your own formatting function, say for zip codes, social security numbers, etc. I have several functions of this nature for different things such as license numbers and zip codes as well.

 

[mresann]

OOPS, forgot to parse the name out of the string.

Here is one way:

Function fncSeparateNameAndPhone(ByVal strInText As String, _
                                 ByRef strOutName As String, _
                                 ByRef strOutPhone As String _
                                 ) As Boolean

PROC_DECLARATIONS:
   Dim strNumber                As String
   Dim lngCounter               As Long
   Dim lngSplit                 As Long
   Dim blnSeparateNameAndPhone  As Boolean
   
PROC_START:
   On Error GoTo PROC_ERROR
   strNumber = "0123456789()-"
   
PROC_MAIN:
   For lngCounter = 1 To Len(strInText)
      If InStr(strNumber, Mid(strInText, lngCounter, 1)) > 0 Then
         lngSplit = lngCounter
         Exit For
      End If
      'incorrect format, get out of function
      blnSeparateNameAndPhone = False
      GoTo PROC_EXIT
   Next
   
   strOutName = Trim(Left(strInText, lngSplit - 1))
   strOutPhone = Trim(Mid(strInText, lngSplit))
   blnSeparateNameAndPhone = True
   
PROC_EXIT:
   fncSeparateNameAndPhone = blnSeparateNameAndPhone
   Exit Function

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: fncSeparateNameAndPhone" & vbCrLf & _
           "Module: Module1"
   GoTo PROC_EXIT

End Function

After this, take the phone string and format it with the previous function.