Converting words to numbers

[Bill Gowan]

I had a teacher ask me if I could write a program that would convert words
to numerical equivalents. It's been a long time since I have done
programming so I am asking for help.

She is going to tell the kids they need to come up with a word that equal
(lets say) 30.

We always assume that a=1 b=2 c=3 d=4 etc.

Can anyone help me?

I managed to get the word that was entered and the length of the word. I
think the next think I have to do is convert the word into an array and then
convert each letter into a number.

The place I am stuck is converting the word into an array that I can work
with.

Thnaks in advance for any help!

 

[Bob Phillips]

You can do it with a formula. Assuming the word is in A1, then use

=SUMPRODUCT(CODE(UPPER(MID(SUBSTITUTE(A1,";",""),ROW(INDIRECT("1:"&LEN(SUBST
ITUTE(A1,";","")))),1)))-64)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Chip]

I have made this macro for you...it is assuming that people put the
word in Range A1, but if you need it to be more versatile let me know.

Sub letterizer()
worder = Range("A1").Value
intcounter = 1
Do Until worder = ""
temp = Left(worder, 1)
If Asc(worder) >= 97 Then
letterval = Asc(worder) - 96
Else
letterval = Asc(worder) - 64
End If

Cells(intcounter, 2).Value = letterval
worder = Right(worder, Len(worder) - 1)
intcounter = intcounter + 1
Loop
End Sub

 

[Bill Gowan]

Thanks for the help! This was exactly what I was looking for.

 

[Bob Phillips]

Here is a function that you can call from the worksheet

Function WordValue(rng)
Dim sWord As String
Dim i As Long
Dim cWord As Long
Dim temp
If TypeName(rng) = "Range" Then
sWord = rng.Value
Else
sWord = rng
End If

For i = 1 To Len(sWord)
temp = Asc(UCase(Mid(sWord, i, 1))) - 64
cWord = cWord + temp
Next i

WordValue = cWord
End Function

Use like


=WordValue(A1)

or

=WordValue("Hello")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Which one was?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bill Gowan]

Actually I am using both for now and I will let the teacher pick the way she
prefers.