Function or Sub to help with text string
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|