Sum up all characters in a given string

kelly mort · May 21, 2019

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:

Code:

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

Code:

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 · May 21, 2019

Here are a few options:

Excel 2012

A B C D E F G H I J K L
1 Word Value Value Value Num Lt Lt Lt Num Letter
2 Kelly 2 2 2 1 A J S 1 A
3 mort 3 3 3 2 B K T 2 B
4 hello 7 7 7 3 C L U 3 C
5 4 D M V 4 D
6 5 E N W 5 E
7 6 F O X 6 F
8 7 G P Y 7 G
9 8 H Q Z 8 H
10 9 I R 9 I
11 1 J
12 2 K
13 3 L
14 4 M
15 5 N
16 6 O
17 7 P
18 8 Q
19 9 R
20 1 S
21 2 T
22 3 U
23 4 V
24 5 W
25 6 X
26 7 Y
27 8 Z

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet14

Array Formulas

Cell Formula
B2 {=MOD(SUM(MOD(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))-65,9)+1)-1,9)+1}
C2 {=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}
D2 {=MOD(SUM(SUMIF($L$2:$L$27,MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1),$K$2:$K$27))-1,9)+1}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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 · May 21, 2019

Okay cool.

But I need a vba script for this.

Thanks

Eric W · May 21, 2019

kelly mort said:
Okay cool.

But I need a vba script for this.

Thanks

You might have mentioned that!

Try:
Excel 2012

	A	B	D	E
1	Letter	Num	Word	Value
2	A	1	Kelly	2
3	B	2	mort	3
4	C	3	hello	7
5	D	4	icy	1
6	E	5	bibi	22
7	F	6
8	G	7
9	H	8
10	I	9
11	J	1
12	K	2
13	L	3
14	M	4
15	N	5
16	O	6
17	P	7
18	Q	8
19	R	9
20	S	1
21	T	2
22	U	3
23	V	4
24	W	5
25	X	6
26	Y	7
27	Z	8

Sheet15

Worksheet Formulas

Cell	Formula
E2	=mysum(D2,$A$2:$B$27)

with this macro:

Code:

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 · May 21, 2019

Maybe something like this
EDIT

Code:

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

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

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

Hope this helps

M.

Eric W · May 21, 2019

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 · May 21, 2019

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 · May 21, 2019

Eric W said:
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 · May 21, 2019

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 · May 21, 2019

Eric W said:
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.

Sum up all characters in a given string

kelly mort

Well-known Member

Excel Facts

Eric W

MrExcel MVP

kelly mort

Well-known Member

Eric W

MrExcel MVP

Marcelo Branco

MrExcel MVP

Eric W

MrExcel MVP

kelly mort

Well-known Member

Marcelo Branco

MrExcel MVP

Eric W

MrExcel MVP

Marcelo Branco

MrExcel MVP

Similar threads

Forum statistics

Share this page

Sum up all characters in a given string

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock