I would use an extra column and a user defined function that converted the words
to digits. If I didn't want the original names, I could convert these cells
with formulas to values and delete the original values.
If that sounds reasonable to you:
Option Explicit
Function ConvertToTel(myStr As String) As String
Dim iCtr As Long
Dim myNum As Long
myNum = -1
For iCtr = Asc("A") To Asc("Z")
Select Case iCtr
Case Asc("A") To Asc("C"): myNum = 2
Case Asc("D") To Asc("F"): myNum = 3
Case Asc("G") To Asc("I"): myNum = 4
Case Asc("J") To Asc("L"): myNum = 5
Case Asc("M") To Asc("O"): myNum = 6
Case Asc("P") To Asc("S"): myNum = 7
Case Asc("T") To Asc("V"): myNum = 8
Case Asc("W") To Asc("Z"): myNum = 9
End Select
If myNum = -1 Then
'skip this character
Else
myStr = Replace(myStr, Chr(iCtr), myNum, 1, -1, vbTextCompare)
End If
Next iCtr
ConvertToTel = myStr
End Function
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
========
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Then into a test cell and type this in B1:
=ConvertToTel(a1)
Where A1 contains the name.
