On Tue, 24 Feb 2009 04:43:01 -0800, Venkatesh V
wrote:


HI,

I require a macro / program or excel based tool -- which gives the total
number based on the numbers assigned to each letter.

here are the number assignation to letters.

AIJQY = 1
DKR = 2
CGLS = 3
DMT = 4
EHNX = 5
UVW = 6
FP = 7
OZ = 8

My requirement is -- i have some 50 names.
copy all the names in a column, run macro or function that will
automatically populate the sum of numbers in each letter of the names (in
next column)

i have tried it. but its geting difficult and time consuming. errors are
creeping up for this simpe program!

For example names could be
A Varsha
G Sanya
V Viveka
..
..

so, could you please provide that program.

Thanks & Regards,
Venkatesh


Desired output examples are always useful.

If I understand you correctly, you would want to convert a string like FO to
7+8 or 15. Is that correct?

That being the case:

1. What about <space? Should that be ignored (or equivalent to zero)?
2. What about the letter "D" which you show twice?

For the sake of this example, I assumed the second D (in DMT) was in error and
deleted it. I also assumed that <space = 0 and that upper case and lower case
letters were to be treated the same.

That being the case, you could download and install Longre's free morefunc.xll
add-in (do a Google search for a source, as the original web site source seems
to be intermittent), and then use this formula (with the string in A1):

=EVAL(LEFT(SETV(REGEX.SUBSTITUTE(A1,
"([AIJQY])|([DKR])|([CGLS])|([MT])|([EHNX])|([UVW])|([FP])|([OZ])|\s",
"[1=1+,2=2+,3=3+,4=4+,5=5+,6=6+,7=7+,8=8+]",,,FALSE)),LEN(GETV())-1))

If you would prefer to use a User Defined Function:

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like
=SumLtrs(cell_ref) in some cell.

================================================== ==
Option Explicit
Option Compare Text
Function SumLtrs(str As String) As Double
Dim i As Long
Dim dTemp As Double
For i = 1 To Len(str)
Select Case Mid(str, i, 1)
Case "A", "I", "J", "Q", "Y"
dTemp = dTemp + 1
Case "D", "K", "R"
dTemp = dTemp + 2
Case "C", "G", "L", "S"
dTemp = dTemp + 3
Case "M", "T"
dTemp = dTemp + 4
Case "E", "H", "N", "X"
dTemp = dTemp + 5
Case "U", "V", "W"
dTemp = dTemp + 6
Case "F", "P"
dTemp = dTemp + 7
Case "O", "Z"
dTemp = dTemp + 8
End Select
Next i
SumLtrs = dTemp
End Function
======================================
--ron