Sum up all characters in a given string

[kelly mort]

Hello,
I have this table and I am looking for a cooler way to use it as a guide and create an algorithm from it.
So from the table, 1 is assigned to A, J and S. So say a word like “KELLY”, I want to add up all the characters by using their number value. That’s “Kelly” = 2+5+3+3+7 = 20.


Then I use these further rules on the outcome:
(1). If the result form the sum is more than a 1-digit number, then we sum the characters of our result up again till we get a single digit answer.
The numbers 11 and 22 are excluded. That is, when our sums yield 11 or 22, we should skip the further sums.
(2). When the result has reached 11 or 22 or a single digit from the other non-single digit numbers, then display that on a message box or the immediate window.
These are sample examples of output I am looking for:

1.    “Kelly” = 2+5+3+3+7 = 20 = 2+0 = 2
2.    “mort” = 4+6+9+2 = 21 = 2+1 = 3
3.    “hello” = 8+5+3+3+6 = 25 = 2+5 = 7

So for “Kelly” result is 2
So for “mort” result is 3
So for “hello” result is 7

Num    LT    LT    LT
1    A    J    S
2    B    K    T
3    C    L    U
4    D    M    V
5    E    N    W
6    F    O    X
7    G    P    Y
8    H    Q    Z
9    I    R

 

[Eric W]

Here are a few options:[TABLE="width: 85%"]
<tbody>[TR]
[TD]Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
Word	Value	Value	Value	Num	Lt	Lt	Lt	Num	Letter
Kelly	A	J	S	A
mort	B	K	T	B
hello	C	L	U	C
D	M	V	D
E	N	W	E
F	O	X	F
G	P	Y	G
H	Q	Z	H
I	R	I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

</tbody>

Sheet14​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=MOD(SUM(MOD(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))-65,9)+1)-1,9)+1}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=MOD(SUM(SUMIF($G$2:$G$10,MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1),$F$2:$F$10),SUMIF($H$2:$H$10,MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1),$F$2:$F$10),SUMIF($I$2:$I$10,MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1),$F$2:$F$10))-1,9)+1}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=MOD(SUM(SUMIF($L$2:$L$27,MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1),$K$2:$K$27))-1,9)+1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]



The formula in B2 generates the results without using a table, just taking calculating the values so they match your table. The much longer formula in C2 uses your table in columns F:I. If you rearrange your table as in K:L, you can use the shorter formula in D2.

Note that none of these formulas handle your 11 or 22 rule. To incorporate that, we'd need to add some helper cells, and possibly know an upper limit to how long your word might be. Failing that, a VBA macro could work.

 

[kelly mort]

Okay cool.

But I need a vba script for this.

Thanks

 

[Eric W]

Okay cool.

But I need a vba script for this.

Thanks

You might have mentioned that!

Try:
Excel 2012

	A	B	C	D	E
Letter	Num	Word	Value
A	Kelly
B	mort
C	hello
D	icy
E	bibi
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=mysum(D2,$A$2:$B$27)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




with this macro:

Function MySum(ByVal MyWord As String, ByVal MyTable As Range)
Dim MyTab As Variant, i As Long, w As Long, s As String

    MyTab = MyTable.Value
    MySum = 0
    For i = 1 To Len(MyWord)
        w = Asc(Mid(UCase(MyWord), i, 1)) - 64
        MySum = MySum + MyTab(w, 2)
    Next i
    
    While MySum > 9 And MySum <> 11 And MySum <> 22
        s = MySum
        MySum = 0
        For i = 1 To Len(s)
            MySum = MySum + Mid(s, i, 1)
        Next i
    Wend
    
End Function

 

[Marcelo Branco]

Maybe something like this
EDIT

Function GetSum(s As String, r As Range)
    Dim i As Long
    
    For i = 1 To Len(s)
        GetSum = GetSum + r.Cells(r.Find(Mid(s, i, 1), lookat:=xlWhole, LookIn:=xlValues).Row - r.Row + 1, 1)
    Next i
    
    If GetSum > 9 And GetSum <> 11 And GetSum <> 22 Then
        GetSum = GetSum Mod 9
    End If
End Function

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Num​

[/TD]
[TD]

LT​

[/TD]
[TD]

LT​

[/TD]
[TD]

LT​

[/TD]
[TD]

Name​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

A​

[/TD]
[TD]

J​

[/TD]
[TD]

S​

[/TD]
[TD]

Kelly​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

B​

[/TD]
[TD]

K​

[/TD]
[TD]

T​

[/TD]
[TD]

mort​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

C​

[/TD]
[TD]

L​

[/TD]
[TD]

U​

[/TD]
[TD]

hello​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

D​

[/TD]
[TD]

M​

[/TD]
[TD]

V​

[/TD]
[TD]

club​

[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

E​

[/TD]
[TD]

N​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

F​

[/TD]
[TD]

O​

[/TD]
[TD]

X​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

G​

[/TD]
[TD]

P​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

8​

[/TD]
[TD]

H​

[/TD]
[TD]

Q​

[/TD]
[TD]

Z​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

9​

[/TD]
[TD]

I​

[/TD]
[TD]

R​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in F2 copied down
=GetSum(E2,$A$2:$D$10)

Hope this helps

M.

 

[Eric W]

Marcelo, I think your version will not give the right result on a word such as biiiii. (Not a real word, but it shows the potential.)

 

[kelly mort]

Hi, @Marcelo Branco and @Eric W,

I like the functions they are doing the work.

However, I will like to avoid the formula part.

I instead of calling it into the cell how do I call it to the MsgBox or maybe assign it to a variable?

I believe it's an easy way but can't see it now.

Lol

 

[Marcelo Branco]

Marcelo, I think your version will not give the right result on a word such as biiiii. (Not a real word, but it shows the potential.)

Eric,

Maybe i have misunderstood the criteria
For biiii my function results in 2 (2+9+9+9+9=38; 38 MOD 9 = 2)
What is the expected result?

M.

 

[Eric W]

Marcelo, we'll need Kelly to validate this, but biiiii = 2 + 9 + 9 + 9 + 9 + 9 = 47. This is not 11 or 22, so you take it mod 9 to get 2. However, 4 + 7 = 11, so I think it should stop there. Also, consider a word such as ii. 9 + 9 = 18. You take this mod 9 to get 0. Shouldn't it be 9?

Kelly, please address the points above. Also, I'm a little unsure what you want. We have created the UDFs to give you the result. Are you talking about assigning it to a VBA variable? You can call either of these functions from within other VBA code. Or how do you want the Msgbox? For example, do you want it so that when someone enters a word in a given cell or range, a msgbox will show up with the result? We can make a change event to do that.

 

[Marcelo Branco]

Marcelo, we'll need Kelly to validate this, but biiiii = 2 + 9 + 9 + 9 + 9 + 9 = 47. This is not 11 or 22, so you take it mod 9 to get 2. However, 4 + 7 = 11, so I think it should stop there. Also, consider a word such as ii. 9 + 9 = 18. You take this mod 9 to get 0. Shouldn't it be 9?

Oh, I had not understood that. I guess you're right.

To fix the case "ii" we should change this
GetSum = GetSum Mod 9
to
GetSum = (GetSum - 1) Mod 9 + 1

M.