Article ID: 210254 - Last Review: October 11, 2006 - Revision: 1.2

ACC2000: How to Convert Letters to Numbers in Phone Numbers

View products that this article applies to.
This article was previously published under Q210254
Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

SUMMARY

This article shows you how to create two user-defined functions that you can use to convert any letter in a phone number to its corresponding digit on a telephone keypad or rotary dial. For example, you can convert the number "425-ACC-ESS9" to its equivalent "425-222-3779" so that dialing is easier.

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
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Back to the top
Keywords: 
kbhowto kbprogramming KB210254
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations