Sum of the alphabet's assigned values till 2 digits in single cell

[Velks]

Hi Experts,

I want to Sum the alphabet's assigned values till 2 digits, where each alphabets are assigned with value which can be changed as and when needed.

Below example might give more clarity on my question. Sorry for my poor language.

I'm having the word "ABCDE" in cell "A2" and I want the Result as "21" in "B2".

Assigned Values are...

A=1 B=2, C=3, D=4, E=5, etc.,

Calculation:

ABCDE
1+2+3+4+5
3+5+7+9
8+3+7
2+1. 21 is the expected result.

BCD
234
57

for ABC 57 is the Result and for DCB 75.

DCB
432
75

Note:
All the double digits are converted in to single digits like 12 as 3, 16 as 7 and so on by MOD 9.

I'm ready to use ASCII codes to assign the dynamic values for alphbats.
Thanks in advance.

 

[FDibbins]

I get this part...
ABCDE
1+2+3+4+5

But how do you get the rest?
3+5+7+9
8+3+7
2+1. 21 is the expected result.

 

[Velks]

Thanks for you quick reply.
1+2 =3
2+3 =5
3+4 =7
4+5 =9

=> 3579
Then, sum of the results as belwo

3+5=8
5+7 =12 = 3 ( 12 MOD 9 = 3)
7+9 =16 = 7 ( 16 MOD 9 = 7)

=> 837

Again, Repeating the above results,
8+3 = 11 = 2
3+7 = 10 = 1
=> 21

We can stop with this result as it reached 2 digits now.
Hope I clarified, thanks for you time and help.

 

[Tetra201]

... Assigned Values are...

A=1 B=2, C=3, D=4, E=5, etc.

And what are the assigned values for J, S, and Z?

 

[Rick Rothstein]

Does this UDF (user defined function) do what you want...

[table="width: 500"]
[tr]
	[td]Function WordSum(Word As String) As Variant
  Dim X As Long, Arr As Variant
  If Len(Word) > 1 Then
    Arr = Split(Application.Trim(Replace(StrConv(Word, vbUnicode), Chr(0), " ")))
    For X = 0 To UBound(Arr)
      Arr(X) = (Asc(Arr(X)) - 64) Mod 9
    Next
    Do Until Join(Arr) Like "# #"
      For X = 0 To UBound(Arr) - 1
        Arr(X) = ((Arr(X) + Arr(X + 1) - 1) Mod 9) + 1
      Next
      ReDim Preserve Arr(UBound(Arr) - 1)
    Loop
    WordSum = Arr(0) & Arr(1)
  Else
    WordSum = ""
  End If
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use WordSum just like it was a built-in Excel function. For example,

=WordSum(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Velks]

And what are the assigned values for J, S, and Z?

I have assigned 1 for J & S and 8 for Z.

 

[Velks]

Does this UDF (user defined function) do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function WordSum(Word As String) As Variant
  Dim X As Long, Arr As Variant
  If Len(Word) > 1 Then
    Arr = Split(Application.Trim(Replace(StrConv(Word, vbUnicode), Chr(0), " ")))
    For X = 0 To UBound(Arr)
      Arr(X) = (Asc(Arr(X)) - 64) Mod 9
    Next
    Do Until Join(Arr) Like "# #"
      For X = 0 To UBound(Arr) - 1
        Arr(X) = ((Arr(X) + Arr(X + 1) - 1) Mod 9) + 1
      Next
      ReDim Preserve Arr(UBound(Arr) - 1)
    Loop
    WordSum = Arr(0) & Arr(1)
  Else
    WordSum = ""
  End If
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use WordSum just like it was a built-in Excel function. For example,

=WordSum(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you so much for your help, you are a real excel master. I followed as you explained, almost it worked for me!
But, I would like to assign the values for ASCII codes manually. For example, value 1 for 49 (1), 65 (A) 74 (J) and 83(S), 106(a), 115 (j) and 97(s).

Request your help to get this modified as this works only for UPPER CASE and not for lower case and numbers or any special characters.

 

[Velks]

[TABLE="width: 294"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Word[/TD]
[TD]Outcome[/TD]
[TD]Expected
Result[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]35[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]66[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]A B[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]A BALL[/TD]
[TD]48[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]A Ball[/TD]
[TD]67[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]22[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]AAa[/TD]
[TD]27[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]Aaa[/TD]
[TD]73[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]33[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]aAA[/TD]
[TD]72[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]aaA[/TD]
[TD]37[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]ABB[/TD]
[TD]34[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]A2B[/TD]
[TD]#VALUE![/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]91[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]16[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Request your help to get this modified as this works only for UPPER CASE and not for lower case and numbers or any special characters.

That is because your example only showed upper case letters... you never mentioned lower case, numbers or special characters, so there was no way for me to know to account for them.

But, I would like to assign the values for ASCII codes manually. For example, value 1 for 49 (1), 65 (A) 74 (J) and 83(S), 106(a), 115 (j) and 97(s).

So you don't want A to be 1, B to be 2, etc. like you showed in your first message? I'm confused... you seem to now be saying that you want the coding to be the ASCII value of the character except for digits which you want to be the number itself and not its ASCII value? Or are you saying you will want to vary the coding maybe making, say, an "A" 1 the first time, 65 another time and still some other value in a future run? If so, how did you want to specify the coding? How about this... on a separate sheet (tell me the name you will give that sheet so I can include it in my code), you put the character in the row whose number you want to assign to it. So, if you wanted an "A" to be 1, you would put the letter "A" in cell A1... if you wanted an asterisk to be 2, you would put an asterisk symbol in cell A2, and so on. That way I could simply read the characters from cell A1 down to cell A256 (assuming you wanted to encode every symbol) into my program and use the row the character came from as its code. I could even make it so that you could specify the column to read from... that way you could set up multiple encodings, one per column, and then tell the program I write which column to use. How does that sound?

 

[Velks]

That is because your example only showed upper case letters... you never mentioned lower case, numbers or special characters, so there was no way for me to know to account for them.




So you don't want A to be 1, B to be 2, etc. like you showed in your first message? I'm confused... you seem to now be saying that you want the coding to be the ASCII value of the character except for digits which you want to be the number itself and not its ASCII value? Or are you saying you will want to vary the coding maybe making, say, an "A" 1 the first time, 65 another time and still some other value in a future run? If so, how did you want to specify the coding? How about this... on a separate sheet (tell me the name you will give that sheet so I can include it in my code), you put the character in the row whose number you want to assign to it. So, if you wanted an "A" to be 1, you would put the letter "A" in cell A1... if you wanted an asterisk to be 2, you would put an asterisk symbol in cell A2, and so on. That way I could simply read the characters from cell A1 down to cell A256 (assuming you wanted to encode every symbol) into my program and use the row the character came from as its code. I could even make it so that you could specify the column to read from... that way you could set up multiple encodings, one per column, and then tell the program I write which column to use. How does that sound?

Thanks for your patience and sorry for the unclear information. I unable to attach the excel sheet hence I am trying to provide as much as possible here.

My assigned values for ASCII codes from 32 to 126 are as follows and I’m using these value sets in below respective 'array formula' to get the sum of word’s assigned values in a cell.

Value set 1

0,0,0,0,0,0,10,0,0,0,13,14,0,0,0,9,0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,3,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,0,0,0,0,0,3,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,0,0,0,0
here 32 (space) = 0, 33 (!) = 0,………,73=9,……… 126 = 0


{=SUM( 1* CHOOSE(CODE(MID(A2, ROW(INDIRECT( "1:" & LEN(A2))), 1))-31, 0,0,0,0,0,0,10,0,0,0,13,14,0,0,0,9,0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,3,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,0,0,0,0,0,3,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,0,0,0,0))}

I used this formula in cells B2, B3, B4 & B5 to get the sum of cells A2, A3, A4 & A5 respectively as per Values set 1.


Value set 2

0,0,0,0,0,0,10,0,0,0,10,20,0,0,0,3,0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,5,1,2,3,4,5,8,3,5,1,1,2,3,4,5,7,8,1,2,3,4,6,6,6,5,1,7,0,0,0,0,0,5,1,2,3,4,5,8,3,5,1,1,2,3,4,5,7,8,1,2,3,4,6,6,6,5,1,7,0,0,0,0

here 32 (space) = 0, 33 (!) = 0,………,73=1,……… 126 = 0

I used this formula in cells D2, D3, D4 & D5 to get the sum of cells A2, A3, A4 & A5 respectively as per Values set 2.


[TABLE="width: 439"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Word
[/TD]
[TD]Value Set 1
[/TD]
[TD]WordSum1
[/TD]
[TD]Value Set 2
[/TD]
[TD]WordSum2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Xyz
[/TD]
[TD]21
[/TD]
[TD]46
[/TD]
[TD]13
[/TD]
[TD]68
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]a2c*r
[/TD]
[TD]28
[/TD]
[TD]25
[/TD]
[TD]18
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1243
[/TD]
[TD]10
[/TD]
[TD]94
[/TD]
[TD]10
[/TD]
[TD]94
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]ABC
[/TD]
[TD]6
[/TD]
[TD]35
[/TD]
[TD]6
[/TD]
[TD]35
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet Name is “Num Calculator” and

I am looking for the WordSum1 in Cell C2 using value set 1 and WordSum2 in Cell E2 using value set 2.

In future, I will assign the values with value set3, value set4 and update the code with WordSum3 & WordSum4 if required.

Thank you.