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.

 

[Rick Rothstein]

Thank you for your time again! Now WordSum works for small &special character, but for few still it gives #Value! error.

e.g *A, A*, +A , A+ and 5*.

So that I can trace the problem down more easily, what should the answer be for each of those for both Value Set 1 and Value Set 2?

 

[Rick Rothstein]

So that I can trace the problem down more easily, what should the answer be for each of those for both Value Set 1 and Value Set 2?

Never mind the above question... I see the problem, another bad editing job on my part, but in a different location. This should work correctly now...

[table="width: 500"]
[tr]
	[td]Function WordSum(Word As String, ValueSet As Long) As Variant
  Dim X As Long, Arr As Variant, ValSet() As String, VSet() As String
  
[B][COLOR="#008000"]  ' This section controls your Value Sets. When you add additional
  ' Value Sets, change the number after the "To" (2 at the start) to
  ' the maximum Value Set number. So, if you add two more Value Sets
  ' (3 and 4 inside the parentheses after the ValSet name), then the
  ' number that follows word "To" would be changed to 4.[/COLOR][/B]
  ReDim ValSet(1 To 2)
  ValSet(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"
  ValSet(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"
  
  VSet = Split(String(32, ",") & ValSet(ValueSet), ",")
  If Len(Word) > 1 Then
    Arr = Split(Application.Trim(Replace(" " & Application.Trim(Replace(StrConv(Word, vbUnicode), Chr(0), " ")), " 0 ", " ")))
    For X = 0 To UBound(Arr)
      Arr(X) = ((VSet(Asc(Arr(X))) - 1) Mod 9) + 1
    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(0 To UBound(Arr) - 1)
    Loop
    WordSum = Arr(0) & Arr(1)
  Else
    WordSum = ""
  End If
End Function[/td]
[/tr]
[/table]

 

[Velks]

Never mind the above question... I see the problem, another bad editing job on my part, but in a different location. This should work correctly now...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function WordSum(Word As String, ValueSet As Long) As Variant
  Dim X As Long, Arr As Variant, ValSet() As String, VSet() As String
  
[B][COLOR=#008000]  ' This section controls your Value Sets. When you add additional
  ' Value Sets, change the number after the "To" (2 at the start) to
  ' the maximum Value Set number. So, if you add two more Value Sets
  ' (3 and 4 inside the parentheses after the ValSet name), then the
  ' number that follows word "To" would be changed to 4.[/COLOR][/B]
  ReDim ValSet(1 To 2)
  ValSet(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"
  ValSet(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"
  
  VSet = Split(String(32, ",") & ValSet(ValueSet), ",")
  If Len(Word) > 1 Then
    Arr = Split(Application.Trim(Replace(" " & Application.Trim(Replace(StrConv(Word, vbUnicode), Chr(0), " ")), " 0 ", " ")))
    For X = 0 To UBound(Arr)
      Arr(X) = ((VSet(Asc(Arr(X))) - 1) Mod 9) + 1
    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(0 To UBound(Arr) - 1)
    Loop
    WordSum = Arr(0) & Arr(1)
  Else
    WordSum = ""
  End If
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Awesome, code does the job perfectly. Thank you so much.

 

[Tetra201]

Just to mention: my UDF from Post #34 can be used to find out what value is assigned to a particular character in a particular set.

For example, =WordSum("*",1) would return 13; =WordSum("*",2) would return 10, etc.

 

[Velks]

Thank you for both of you for your significant help provided.

The given codes are working perfectly in my laptop with macro enabled excel but in my smartphone excel macro is not working. It will be great help if I could get this done in excel using any formulas without using macro.

I’m okay to have results in 2 or 3 cells if cannot get the result in single cell using the formula without macro.

Example

Say, word is A 5 * Hotel

For Value set 1

Word A 5 * Hotel
Values 1 5 13 86253

Results can be as below

Cell 1 = 1 5 13 8 6 2 5 3 ………. Values
Cell 2 = 43 (1+5+13+8+6+2+5+3) ………. LetterSum
Cell 3 = 78 ………. WordSum


For Value set 2
Word A 5 * Hotel
Values 1 5 10 86253

Results can be as below

Cell 1 = 1 5 10 8 6 2 5 3 ………. Values
Cell 2 = 40 (1+5+10+8+6+2+5+3) ………. LetterSum
Cell 3 = 14 ………. WordSum

 

[Velks]

Just to mention: my UDF from Post #34 can be used to find out what value is assigned to a particular character in a particular set.

For example, =WordSum("*",1) would return 13; =WordSum("*",2) would return 10, etc.

Yes, Thanks

 

[XOR LX]

It will be great help if I could get this done in excel using any formulas without using macro.

I’m okay to have results in 2 or 3 cells if cannot get the result in single cell using the formula without macro.

Due to the iterative process required, any single-cell formula solution for the WordSum will be unfeasible.

I can offer you a formula-based set-up which uses a cell for each iteration for a given input string, until the desired two-digit solution is reached. So, for example, for "A BALL" in cell A1, we would return:

In B1: 12133
In C1: 3346
In D1: 671
In E1: 48

The formula in C1 is dragged to the right a sufficient number of columns until the desired result is achieved (blanks result in columns beyond this point). These can be followed by, if you prefer, a final formula in some column to the right (e.g. column L) which returns the desired results from each row into the same column.

Let me know if that is something you would be interested in.

Regards

 

[Velks]

Sure.. I'm happy to accept your suggestions that also helps me.

 

[XOR LX]

Although we can hard-code the entries from the Value Set within the actual formula - as you did - I think it's preferable to store them in a table within the actual workbook. This both reduces formula length and also offers greater flexibility if and when changes to those entries are required.

As such, assuming that this table is within a sheet named 'Data', with characters and assigned values in A2:A96 and B2:B96 respectively, enter this array formula** in B2 (of a different worksheet, obviously), for a chosen string in A2:

=INT(NPV(-0.9,MOD(INDEX(Data!B$2:B$96,N(IF(1,MATCH(MID(SUBSTITUTE(A2," ",""),1+LEN(SUBSTITUTE(A2," ",""))-COLUMN(INDEX(1:1,1):INDEX(1:1,LEN(SUBSTITUTE(A2," ","")))),1),IF(MMULT(0+EXACT(Data!A$2:A$96,MID(SUBSTITUTE(A2," ",""),COLUMN(INDEX(1:1,1):INDEX(1:1,LEN(SUBSTITUTE(A2," ","")))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A2," ",""))))),Data!A$2:A$96),0)))),9)/10))

Then this array formula** in C2:

=IF(N(B2)<100,"",INT(NPV(-0.9,(1+MOD(MMULT(INDEX(1+MOD(MID(B2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B2))),1)-1,9),N(IF(1,{0,1}+LEN(B2)-ROW(INDEX(A:A,1):INDEX(A:A,LEN(B2)-1))))),{1;1})-1,9))/10)))

Copy the latter to the right until you start to get blanks for the results. The last non-blank is your desired result.

If, as I mentioned, you would prefer an additional formula in some column to the right which returns this result, then, assuming you copy the formula in C2 as far as K2, put this in e.g. L2:

=LOOKUP(9.9E+307,B2:K2)

Obviously all these formulas may be copied down to give similar results for strings in A3, A4, etc.

Unfortunately, due to the nature of these constructions, the string being queried cannot exceed 15 characters in length.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Velks]

Copy the latter to the right until you start to get blanks for the results. The last non-blank is your desired result.

If, as I mentioned, you would prefer an additional formula in some column to the right which returns this result, then, assuming you copy the formula in C2 as far as K2, put this in e.g. L2:

=LOOKUP(9.9E+307,B2:K2)

Obviously all these formulas may be copied down to give similar results for strings in A3, A4, etc.

Unfortunately, due to the nature of these constructions, the string being queried cannot exceed 15 characters in length.

Thank you... But I'm not. Getting the above part