Converting Phone Numbers

@J.Woolley
Thanks for your appreciation. Take also a look at Rick Rothstein's alternatives in the <2007 version of this tip.

@Willy
I loved your macro, so I added the following function to My Excel Toolbox (with credit to you):
=PhoneTextToDigits(PhoneNumber, [AsNumeric])
It returns either formatted text (default) or a numeric value; the latter permits custom cell formatting or use of the TEXT function.
Here is an abreviated version:

Function PhoneTextToDigits(PhoneNumber As String, Optional AsNumeric As Boolean)
'see Willy Vanhaelen, https://excelribbon.tips.net/T011802_Converting_Phone_Numbers.html
    Dim sNum As String, sDig As String, n As Integer
    sNum = UCase(PhoneNumber)
    For n = 1 To Len(sNum)
        sDig = Mid(sNum, n, 1)
        If sDig Like "[A-Z]" Then
            Mid(sNum, n, 1) = Mid("22233344455566677778889999", (Asc(sDig) - 64), 1)
        ElseIf AsNumeric And sDig Like "[!0-9]" Then
            Mid(sNum, n, 1) = " "
        End If
    Next n
    If AsNumeric Then
        PhoneTextToDigits = CDec(Replace(sNum, " ", ""))
    Else
        PhoneTextToDigits = sNum
    End If
End Function

See https://sites.google.com/view/MyExcelToolbox/

@John Man
In vba Mid can be used in two ways:
1) the Mid statement: replaces a specified number of characters in a Variant (String) variable with characters from another string.
Syntax: Mid(stringvar, start, [ length ] ) = string
2) the Mid function: Returns a Variant (String) containing a specified number of characters from a string.
Syntax: Mid(string, start, [ length ])

The statement Mid(phone,J,1) replaces the character at position J in the phone string, only 1, with the result of
the function Mid("22233344455566677778889999", Asc(digit)-64,1).

"22233344455566677778889999" is the of string of digits to search from
The variable digit is the character to be replaced by the corresponding cipher.
Asc(digit)-64 returns the ascii character code from digit and subtracts 64 form it resulting in a numuber 1 tot 26.

So A,B and C will be replced by 2 ... W,X,Y and Z by 9.

@Willy. I'm a very raw VBA newbie, but I oftern try to follow along on these scripts. I'm having diffuculty understanding one line of your version.

The line I'm puzzled by reads "Mid(phone,J,1)=Mid("22233344455566677778889999", Asc(digit)-64,1)". I don't see how you pick off which digit to use from the long string following the equals sign.

Thanks for any explanation.

The macro in this trip is rather complicated. It also replaces each digit, hyphen and space with it own which is a waist of tilme.

Here is my version that is smaller and replaces only the letters making it faster:

Sub DoPhone2()
Dim cell As Range, digit As String, J As Integer, phone As String
For Each cell In Selection
   If Not cell.HasFormula Then
   phone = UCase(cell)
      For J = 1 To Len(phone)
         digit = Mid(phone, J, 1)
         If digit Like "[A-Z]" Then
            Mid(phone,J,1)=Mid("22233344455566677778889999", Asc(digit)-64,1)
         End If
      Next J
   cell = phone
   End If
   Next cell
End Sub