Microsoft KB Archive/210254

= ACC2000: How to Convert Letters to Numbers in Phone Numbers =

Article ID: 210254

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210254



Moderate: Requires basic macro, coding, and interoperability skills.



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:   Create a module and type the following line in the Declarations section if it is not already there: Option Explicit   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   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   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



Additional query words: inf

Keywords: kbhowto kbprogramming KB210254

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.