change letter to number add number
 

Hello,

I need help writing a macro that will change letters to numbers and add
the numbers for a total.
Using the alphbet with special values for each letter.
Example:

CELL A1 is rudy
r = 1
u = 2
d = 4
y = 3

total = 10

So if cell A1 = Rudy then B1 will show 10

if A2 = Rud then B2 will show 9

if A3 = B L A N K then B3 will show nothing

Thanks in advance,
Bill

change letter to number add number
 

a-i = 1-9 ==== j-r = 1-9 ====== s-z = 1-8

a=1, b=2, c=3, d=4, e=5, f=6, g=7, h=8, i=9,
j=1, k=2, etc..................

objective is to write a name like ANGEL then formula
ANGEL = 15753 = sum it to 1+5+7+5+3=21=3

Result is ANGEL(Cell A1) = 3(cell B1)
user types ANGEL in column A gets result in column B

Thanks

change letter to number add number
 

You've changed the rules a bit from the first posting, but here is a
UDF to do what you asked for in your later post:

Function txt_num(my_name As String)
' Pete Ashurst, 29/03/2006
'
Dim my_num As Long
Dim i As Long
Dim char As Long
txt_num = 0
If Len(my_name) = 0 Then Exit Function
my_name = LCase(my_name)
my_num = 0
For i = 1 To Len(my_name)
char = Asc(Mid(my_name, i, 1))
If char < 97 Or char 122 Then Exit Function
my_num = my_num + ((char - 97) Mod 9 + 1)
Next i
my_num = Int(my_num / 1000) + Int(my_num / 100) _
+ Int(my_num / 10) + my_num Mod 10
txt_num = Int(my_num / 10) + my_num Mod 10
End Function

Type your name into A1 and use this formula in B1:

=txt_num(A1)

The formula can be copied down for as many names as you have in column
A. The UDF will return 0 if you have any characters other than A to Z
or a to z or if there is nothing in the corresponding cell of column A.

Hope this is what you wanted.

Pete