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.

 

[Velks]

Here is an updated code to include clarifications from Post #18.

To include additional sets, just create ValArr3 = Array(......), ValArr4 = Array(......), etc., and list their members similarly to ValArr1.

Then, add the new arrays to ValArr like this: ValArr = Array(ValArr1, ValArr2, ValArr3, ValArr4). That's it.

Option Base 1
Function WordSum(InpStr As String, Optional SetNo As Byte = 1) As Byte
    SymArr = Array(32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126)
    ValArr1 = Array(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)
    ValArr2 = Array(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)
    ValArr = Array(ValArr1, ValArr2)
    n = Len(InpStr)
    If n = 0 Then Exit Function
    ReDim TmpArr(1 To n)
    For i = 1 To n
        TmpArr(i) = ValArr(SetNo)(Application.Match(Asc(Mid(InpStr, i, 1)), SymArr, 0))
    Next i
    Select Case n
        Case 1: WordSum = TmpArr(1): Exit Function
        Case 2
            For i = 1 To 2
                TmpArr(i) = TmpArr(i) \ 10 + TmpArr(i) Mod 10
            Next i
        Case Else
            Do
            n = n - 1
            For i = 1 To n
                TmpArr(i) = ((TmpArr(i) + TmpArr(i + 1) - 1) Mod 9) + 1
            Next i
            Loop Until n = 2
    End Select
    WordSum = TmpArr(1) * 10 + TmpArr(2)
End Function

Thanks for your guidance. I have updated new code and tried but MOD 9 is not working for *A and A* (code result 131 & 23 , expected result 41 & 14) and for A+ and +A ( code result 141 & 24; expected results are 15 & 51) Could you please help on this.

 

[Tetra201]

For "*A", the function returns the same results as for "*1".
For "A*", the function returns the same results as for "1*".
For "+A", the function returns the same results as for "+1".
For "A+", the function returns the same results as for "1+".
Should they be different?

 

[Velks]

Thanks for your guidance. I have updated new code and tried but MOD 9 is not working for *A and A* (code result 131 & 23 , expected result 41 & 14) and for A+ and +A ( code result 141 & 24; expected results are 15 & 51) Could you please help on this.

Also,
if space has been given in between any letter/ word the results goes wrong, please help.

 

[Velks]

Yes...Same Result. No Difference.

Actually, first mod should be applied for * value 13 and then it should calculate as per existing code. So, *A => MOD(13,9)1 => 41.

 

[Tetra201]

Also, if space has been given in between any letter/ word the results goes wrong, please help.

Lets' see. Per Post #10, the space chraracter (Chr(32)) has the assigned value of 0.

So, for "A B", it's:
1~0~2 ==> 1~2 ==> 12. That's exactly what my function from Post #21 returns.

For "A BALL", it's:
1~0~2~1~3~3 ==> 1~2~3~4~6 ==> 3~5~7~1 ==> 8~3~8 ==> 2~2 ==> 22. That's exactly what my function from Post #21 returns.

 

[Tetra201]

... Actually, first mod should be applied for * value 13 and then it should calculate as per existing code. So, *A => MOD(13,9)1 => 41.

My function from Post #21 returns the desired result of 41 for "*A".

 

[Velks]

Lets' see. Per Post #10, the space chraracter (Chr(32)) has the assigned value of 0.

So, for "A B", it's:
1~0~2 ==> 1~2 ==> 12. That's exactly what my function from Post #21 returns.

For "A BALL", it's:
1~0~2~1~3~3 ==> 1~2~3~4~6 ==> 3~5~7~1 ==> 8~3~8 ==> 2~2 ==> 22. That's exactly what my function from Post #21 returns.

Thank you it's my miss. Can we able to remove that zero for space and consider null value please.

 

[Tetra201]

Thank you it's my miss. Can we able to remove that zero for space and consider null value please.

Here is an updated code modified to exclude spaces from the calculations:

Option Base 1
Function WordSum(ByVal InpStr As String, Optional SetNo As Byte = 1) As Byte
    SymArr = Array(32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126)
    ValArr1 = Array(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)
    ValArr2 = Array(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)
    ValArr = Array(ValArr1, ValArr2)
    InpStr = Replace(Application.Trim(InpStr), " ", "")
    n = Len(InpStr)
    If n = 0 Then Exit Function
    ReDim TmpArr(1 To n)
    For i = 1 To n
        TmpArr(i) = ValArr(SetNo)(Application.Match(Asc(Mid(InpStr, i, 1)), SymArr, 0))
    Next i
    Select Case n
        Case 1: WordSum = TmpArr(1): Exit Function
        Case 2
            For i = 1 To 2
                TmpArr(i) = TmpArr(i) \ 10 + TmpArr(i) Mod 10
            Next i
        Case Else
            Do
            n = n - 1
            For i = 1 To n
                TmpArr(i) = ((TmpArr(i) + TmpArr(i + 1) - 1) Mod 9) + 1
            Next i
            Loop Until n = 2
    End Select
    WordSum = TmpArr(1) * 10 + TmpArr(2)
End Function

 

[Velks]

Lets' see. Per Post #10, the space chraracter (Chr(32)) has the assigned value of 0.

So, for "A B", it's:
1~0~2 ==> 1~2 ==> 12. That's exactly what my function from Post #21 returns.

For "A BALL", it's:
1~0~2~1~3~3 ==> 1~2~3~4~6 ==> 3~5~7~1 ==> 8~3~8 ==> 2~2 ==> 22. That's exactly what my function from Post #21 returns.

Yes
It works perfect. Please help to skip the calculation for wherever 0 comes.

For Example, "A B" , i want like 1 2 = => 12 or 1 0 2 ==> 12

Thanks.

 

[Velks]

Here is an updated code modified to exclude spaces from the calculations:

Option Base 1
Function WordSum(ByVal InpStr As String, Optional SetNo As Byte = 1) As Byte
    SymArr = Array(32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126)
    ValArr1 = Array(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)
    ValArr2 = Array(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)
    ValArr = Array(ValArr1, ValArr2)
    InpStr = Replace(Application.Trim(InpStr), " ", "")
    n = Len(InpStr)
    If n = 0 Then Exit Function
    ReDim TmpArr(1 To n)
    For i = 1 To n
        TmpArr(i) = ValArr(SetNo)(Application.Match(Asc(Mid(InpStr, i, 1)), SymArr, 0))
    Next i
    Select Case n
        Case 1: WordSum = TmpArr(1): Exit Function
        Case 2
            For i = 1 To 2
                TmpArr(i) = TmpArr(i) \ 10 + TmpArr(i) Mod 10
            Next i
        Case Else
            Do
            n = n - 1
            For i = 1 To n
                TmpArr(i) = ((TmpArr(i) + TmpArr(i + 1) - 1) Mod 9) + 1
            Next i
            Loop Until n = 2
    End Select
    WordSum = TmpArr(1) * 10 + TmpArr(2)
End Function

Thanks a lot, Excellent!!! Appreciate your patience; I’m Grateful to you for your help!!!