Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Convert Letters to Numbers in Phone Numbers

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.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Keywords: KB210254, kbprogramming, kbhowto

↑ Back to the top

Article Info
Article ID : 210254
Revision : 3
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 353