Function or Sub to help with text string

[Jack Deuce]

I'm hoping someone has already done this or can direct me to a site
that has the code. I'm lousy at text messaging. It takes a long time
to find the letter on my phone (Tracfone with no special options) then
complete the message. I've setup the lookup table assigning each
letter the value from the keypad. The letter "C" for example is 3
presses of the 2 key, 1st for A, 2nd for B. So pressing it twice the
string is 22. The letter V would be 888 because V is entered when the
8 key is pressed the 3rd time. The lookup table is structured thusly.

Col A Col B
A 2
B 22
C 222
D 3
....
W 9
X 99
Y 999
Z 9999

I'd like to enter the text message in one cell and in the other have
that string evaluated by substituting the value in lookup column B for
that character representation. The word HELLO would translate to
4433555555666 H=44 E=33 L=555 L=555 O=666. I haven't been able to
find a function that would transform each text character to the
numeric pattern. Once the transform is complete, I'd just have to
look at the returned string to type the message. I assigned a space
the character code of 247.

Hope someone can help. Thanks.

 

[Claus Busch]

Hi Jack,

Am Tue, 11 Jun 2013 08:21:30 -0500 schrieb Jack Deuce:

I'd like to enter the text message in one cell and in the other have
that string evaluated by substituting the value in lookup column B for
that character representation. The word HELLO would translate to
4433555555666 H=44 E=33 L=555 L=555 O=666. I haven't been able to
find a function that would transform each text character to the
numeric pattern. Once the transform is complete, I'd just have to
look at the returned string to type the message. I assigned a space
the character code of 247.

try this function:
Function DialWord(myRange As Range) As String
Dim i As Integer
Dim myStr As String
Dim c As Range

For i = 1 To Len(myRange)
Set c = Range("A1:A26").Find(Mid(myRange, i, 1), _
LookIn:=xlValues).Offset(0, 1)
myStr = myStr & c.Value & "-"
Next
DialWord = Left(myStr, Len(myStr) - 1)
End Function

If you write "Hello" in D1, then in another cell:
=DialWord(D1)
The result will be:
44-33-555-555-666


Regards
Claus Busch

 

[Claus Busch]

Hi again,

Am Tue, 11 Jun 2013 16:10:28 +0200 schrieb Claus Busch:

If you write "Hello" in D1, then in another cell:
=DialWord(D1)
The result will be:
44-33-555-555-666

if you want to write a sentence in D1, then change the code:

Function DialWord(myRange As Range) As String
Dim i As Integer
Dim myStr As String
Dim myStr1 As String
Dim c As Range

For i = 1 To Len(Trim(myRange))
If Mid(Trim(myRange), i, 1) = " " Then
myStr1 = myStr1 & "|" & Right(myStr, Len(myStr) - 1)
myStr = ""
End If
Set c = Range("A1:A26").Find(Mid(Trim(myRange), i, 1), _
LookIn:=xlValues)
If Not c Is Nothing Then
myStr = myStr & "-" & c.Offset(0, 1)
End If
Next
If myStr1 = "" Then
DialWord = Right(myStr, Len(myStr) - 1)
Else
DialWord = Right(myStr1, Len(myStr1) - 1) & "|" _
& Right(myStr, Len(myStr) - 1)
End If
End Function


Regards
Claus Busch

 

[Jack Deuce]

Hi again,

Am Tue, 11 Jun 2013 16:10:28 +0200 schrieb Claus Busch:


if you want to write a sentence in D1, then change the code:

Function DialWord(myRange As Range) As String
Dim i As Integer
Dim myStr As String
Dim myStr1 As String
Dim c As Range

For i = 1 To Len(Trim(myRange))
If Mid(Trim(myRange), i, 1) = " " Then
myStr1 = myStr1 & "|" & Right(myStr, Len(myStr) - 1)
myStr = ""
End If
Set c = Range("A1:A26").Find(Mid(Trim(myRange), i, 1), _
LookIn:=xlValues)
If Not c Is Nothing Then
myStr = myStr & "-" & c.Offset(0, 1)
End If
Next
If myStr1 = "" Then
DialWord = Right(myStr, Len(myStr) - 1)
Else
DialWord = Right(myStr1, Len(myStr1) - 1) & "|" _
& Right(myStr, Len(myStr) - 1)
End If
End Function


Regards
Claus Busch

Thanks for help Claus. I've entered the Function and added 0-9 to
include them in the range data and have changed the code defining the
Range("A1:A37"). I'm getting the #value when a space is detected in
D1. I have it defined in the range A1-A37 (A-Z,1-0,space). IOW, I'm
not seeing the "-" when a space is entered separating words. Thanks
again for your help.

 

[Claus Busch]

Hi Jack,

Am Wed, 12 Jun 2013 10:51:05 -0500 schrieb Jack Deuce:

Thanks for help Claus. I've entered the Function and added 0-9 to
include them in the range data and have changed the code defining the
Range("A1:A37"). I'm getting the #value when a space is detected in
D1. I have it defined in the range A1-A37 (A-Z,1-0,space). IOW, I'm
not seeing the "-" when a space is entered separating words. Thanks
again for your help.

you don't have to enter a space into the range. The space is checked in
the string.
Have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "DialWords" and download it, because macros are
disabled in SkyDrive


Regards
Claus Busch

 

[Jack Deuce]

Hi Jack,

Am Wed, 12 Jun 2013 10:51:05 -0500 schrieb Jack Deuce:


you don't have to enter a space into the range. The space is checked in
the string.
Have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "DialWords" and download it, because macros are
disabled in SkyDrive


Regards
Claus Busch

I see that now. Thanks for your help. You're the best. It works
great.