Name number program

[Venkatesh V]

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

 

[Joel]

Check your look up table. You should have each letter listed only once. You
are missing the Letter B and have the letter D twice.

 

[Bernard Liengme]

Copy this UDF to a general module
If the first name is in A1, then in B1 enter =TRYME(A1)
Copy down the column
Please note I have used BMT worth 4 and DKR worth 2 as you had omitted B and
included two Ds
Change as needed

Function Tryme(mycell)
mylen = Len(mycell)
For j = 1 To mylen
mytest = UCase(Mid(mycell, j, 1))
If InStr(1, "AIJQY", mytest) > 0 Then
mysum = mysum + 1
ElseIf InStr(1, "DKR", mytest) > 0 Then
mysum = mysum + 2
ElseIf InStr(1, "CGLS", mytest) > 0 Then
mysum = mysum + 3
ElseIf InStr(1, "BMT", mytest) > 0 Then
mysum = mysum + 4
ElseIf InStr(1, "EHNX", mytest) > 0 Then
mysum = mysum + 5
ElseIf InStr(1, "UVW", mytest) > 0 Then
mysum = mysum + 6
ElseIf InStr(1, "FP", mytest) > 0 Then
mysum = mysum + 7
ElseIf InStr(1, "OZ", mytest) > 0 Then
mysum = mysum + 8
End If
Next j
Tryme = mysum
End Function

best wishes

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

Assuming Indian Numerology, the 'D' located in the "2" group is supposed to
be a 'B'. This formula will produce the sum you want (it treats upper and
lower case letters the same)...

=SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1),
" AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5))

 

[Rick Rothstein]

=SUMPRODUCT(INT((FIND(MID(UPPER(A1),ROW($1:$999),1),

" AIJQYBKR CGLS DMT EHNX UVW FP OZ")-1)/5))

By the way... don't modify the spacing in any way... I know it looks funny,
but the exact spacing I have used is critical to the proper functioning of
the formula.

 

[Venkatesh V]

excellent work.

THanks a ton

By the way... don't modify the spacing in any way... I know it looks funny,
but the exact spacing I have used is critical to the proper functioning of
the formula.