Function for telephone keypress letters to numbers

X

xlkeuk

Board Regular
Joined
Jan 4, 2003
Messages
69
Hello, does anyone know if such a function exists?

I have words in column A and want to produce a string of numbers relating to the key you would have to press to make the word. I began by putting 1, 2, 3 etc in the row beginning at B2 and copying this formula from B2 down and along. Then in cell AA2 I combined all the numbers to make the string.

=IF(ISERROR(CODE(MID($A2,B$1,1))),"",IF(OR(CODE(MID($A2,B$1,1))>105,CODE(MID($A2,B$1,1))>115),"",IF(CODE(MID($A2,B$1,1))<100,2,IF(CODE(MID($A2,B$1,1))<103,3,IF(CODE(MID($A2,B$1,1))<106,4,"")))))&IF(ISERROR(CODE(MID($A2,B$1,1))),"",IF(OR(CODE(MID($A2,B$1,1))<106,CODE(MID($A2,B$1,1))>115),"",IF(CODE(MID($A2,B$1,1))<109,5,IF(CODE(MID($A2,B$1,1))<112,6,IF(CODE(MID($A2,B$1,1))<116,7,"")))))&IF(ISERROR(CODE(MID($A2,B$1,1))),"",IF(OR(CODE(MID($A2,B$1,1))<106,CODE(MID($A2,B$1,1))<116),"",IF(CODE(MID($A2,B$1,1))<119,8,IF(CODE(MID($A2,B$1,1))<123,9,""))))

That is what I want to do, but with a function.

The rules are that:

A or a or B or b or C or c = 2
D or d or E or e or F or f = 3
G or g or H or h or I or i = 4
J or j or K or k or L or l = 5
M or m or N or n or O or o = 6
P or p or Q or q or R or r or S or s = 7
T or t or U or u or V or v = 8
W or w or X or x or Y or y or Z or z = 9

I want to be able to write the string "Hello" and produce the result: 43556

Hope you can help, many thanks.
 
Sort by date Sort by votes
Book2
ABCD
1Hello4355643556
2
Sheet1


You need a flexible concatenator function.

Formulas...

B1:

=--ACONCAT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),{"A";"D";"G";"J";"M";"P";"T";"W"})+1)

C1:

=--MCONCAT(MATCH(MID(A1,INTVECTOR(LEN(A1),1),1),{"A";"D";"G";"J";"M";"P";"T";"W"})+1)

You need to confirm whichever formula you chose with control+shift+enter, not with just enter.

The formula in C1 needs:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

which is a work-alike of MCONCAT that the formula in C1 uses. The 2nd formula works faster and needs the morefunc.xll add-in.
 
Upvote 0
I knew Aladin would be with something that didn't use VB !!!
(Edit - I stand corrected, I have just reveiwed his post more...he is using VB)
I'm glad I can still rely on you.

Here is the version that I created using a UDF paste this into a normal module and then in Excel just call it as follows...

=TeleChrs("Hello")

Code: 
Function TeleChrs(stg As String)

TeleChrs = ""

For i = 1 To Len(stg)

    ch = Mid(stg, i, 1)
    
    Select Case ch
    
        Case "A", "a", "B", "b", "C", "c"
            TeleChrs = TeleChrs & 2
        Case "D", "d", "E", "e", "F", "f"
            TeleChrs = TeleChrs & 3
        Case "G", "g", "H", "h", "I", "i"
            TeleChrs = TeleChrs & 4
        Case "J", "j", "K", "k", "L", "l"
            TeleChrs = TeleChrs & 5
        Case "M", "m", "N", "n", "O", "o"
            TeleChrs = TeleChrs & 6
        Case "P", "p", "Q", "q", "R", "r", "S", "s"
            TeleChrs = TeleChrs & 7
        Case "T", "t", "U", "u", "V", "v"
            TeleChrs = TeleChrs & 8
        Case "W", "w", "X", "x", "Y", "y", "Z", "z"
            TeleChrs = TeleChrs & 9
        Case Else
            TeleChrs = TeleChrs & ch
    End Select
Next i

End Function

I have added in one extra thing sometimes these Text numbers start with a number 0800-hello etc If it does not have a normal letter of the alphabet it will use the character that is there...this will be fine for normal numbers but will kick up a problem if you put in funny characters like "!" .
 
Upvote 0
Here's a late entry that's also a non-UDF, non-MoreFunc.xll solution. The formula in cell B2 is:

=SUMPRODUCT((MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),{" ";"A";"D";"G";"J";"M";"P";"T";"W"}))*10^(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))))

A simpler "special-case" formula to handle 7 digits is in cell C2:

=SUMPRODUCT((MATCH(MID(A2,{1;2;3;4;5;6;7},1),{" ";"A";"D";"G";"J";"M";"P";"T";"W"}))*10^({6;5;4;3;2;1;0}))

It is adapted in cells C3:C4 to accommodate 10 digits. Both formula styles convert non-alphabetic characters to "1", which can be eliminated by using the SUBSTITUTE() function, if desired.

--Tom

(cells B2:C4 below are formatted as Special/Phone Number)
MrExcelTele#Conv.xls
ABCD
1Ph#LettersNumberNumber
2ETPhone387-4663387-4663
3HomeAlone1(466)325-6631(466)325-6631
4ETFoneHome(383)663-4663(383)663-4663
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
352
gtd526
gtd526
M
  • Question Question
VBA Code - Run Code if cell value is not blank
Replies
0
Views
654
MHamid
M
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
315
MBarrett
M
D
  • Solved
Save .txt file in UTF-8 instead of ANSI
Replies
9
Views
3K
KOKOSEK
KOKOSEK
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
378
Cquake
C

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top