ACC2000: How to Convert Letters to Numbers in Phone Numbers (210254)

MORE INFORMATION

The method in this article uses two user-defined functions. The first function translates letters into numbers; the second function applies the translation to an entire phone number. The method works with phone numbers that have different formats (for example, "800-555-GIGO" or "800555GIGO") as well as with phone numbers that have different lengths (for example, "555-GIGO" or "011-01-704-555-GIGO").

To convert letters in a phone number to numbers, follow these steps:

1. Create a module and type the following line in the Declarations section if it is not already there:

    Option Explicit

2. Type the following procedure:

   ' Translates a letter to a digit.
   
    Function XlateDigit(ByVal C As String) As String
      C = UCase(C)
      Select Case C
        Case "A" To "P"
          XlateDigit = Chr$((Asc(C) + 1) \ 3 + 28)
        Case "R" To "Y"
          XlateDigit = Chr$(Asc(C) \ 3 + 28)
        Case "Q", "Z"
          XlateDigit = "0"
        Case Else
          XlateDigit = C
      End Select
    End Function

3. Type the following procedure:

    ' Applies the translated digit to a phone number.
   
    Function PhoneLettersToDigits(ByVal PhoneNo As Variant) As Variant
      Dim I as Integer
        If VarType(PhoneNo) = 8 Then  ' A string.
          For I = 1 To Len(PhoneNo)
             Mid(PhoneNo, I, 1) = XlateDigit(Mid(PhoneNo, I, 1))
          Next I
        End If
      PhoneLettersToDigits = PhoneNo
    End Function

4. To test these functions, type the following line in the Immediate window, and then press ENTER:
   

    ? PhoneLettersToDigits("PRO-GRA-MMER")

   Note that the following number is displayed:

   776-472-6637