Monday, August 17, 2009

Excel VBA convert character in string to position in alpahabet

If for some reason you need to convert a string of characters to their corresponding position in the alphabet, here's a dirty little sample of how to do it. The sub below takes the text in B6 and converts each character to a corresponding alphabetic position (i.e. A = 1, B = 2, etc.). It then goes so far as to add the associated integers and plops the sum in B7.

This was motivated by "attitude" equalling 100, while "hard work" only equals 98.

Have fun.

Sub mySub()

myString = UCase(Range("B6").Text)
myValue = 0

For i = 0 To Len(myString) - 1

myStringChar = Mid(myString, i + 1, 1)

For j = 1 To 26
myChar = Mid(myAlphabet, j, 1)
myVal = Replace(myStringChar, myChar, j)
If IsNumeric(myVal) Then
myValue = myValue + CInt(myVal)
Exit For
End If
Next j
Next i

Range("B7").Value = myValue

End Sub

No comments:

Post a Comment