Excel formula to convert sentence in number

Hai,

I have came across to some interesting question by a management. The question is can we add letter as numbers. Meaning each letter assigned a number and it should add or sum the numbers.

I have letter A-Z in column A and number assigned to each letter in Column B.

So if user type any word or words then it should sum the numbers and give me the total.

For Example:

Example 1
C = E
E = 5
L = 12
X = 24

So the word is Excel which is equal to 5+24+3+5+12 = total 39

Example 2
W = 23
o = 15
r = 18
l = 12
d = 4

So the word is Excel which is equal to 23+15+18+12+4 = total 72

Appreciate if some1 can help me to get formula or VBA function as my data is huge with 700K - 900K rows

Thanks Olly for your response.

I forget to mentioned that numeric values are not fix to as same as mentioned above. Above mentioned number is the example only.

A value can be 100 or the user input in the column B

Let me explain.

I have letter in column A and numbers in column B.

The function or formula look for that letter in Column A and see what is the number next to it in Column B.

It need to check entire string in Column C which is a big list and sum the total.

I hope I have explained correctly to make you understand. If not please give me some time to prepare some samples and attached a worksheet.

Thanks 6StringJazzer for converting my hard words into simple explanation.

This is what I am trying to say.

How ever I am attaching a sample for your reference.

Thanks dmcgov for your script but it is not working also I have huge range of sentences not only 2 cell. The above is the example how is the input.

what line does it fail on (choose debug) and what is the error. it is working fine on my side

Another option

Function SumString(St As String) As Double
   Dim cl As Range
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A1:B26")
         .Item(UCase(cl.Value)) = cl.Offset(, 1).Value
      Next cl
      For i = 1 To Len(St)
         SumString = SumString + .Item(UCase(Mid(St, i, 1)))
      Next i
   End With
End Function

Thanks Fluff13. It is working for me if the letter are English. What about I it is not English.

Meaning any other language. It will not work.

What I am trying to tell you is any string in column A (Any Language) should work because it is assuming as a string not a language right.

As long as you have the letters/symbols in col A then it should work.

I mean to say if I put letters Arabic or hindi or Urdu it should work right but it is not working.

In that case, please supply a sample workbook with representative data.

Dear Fluff13.

Please find the attachment for 2 more languages (Arabic & Urdu) sample workbook s requested.

Ok, try this

Function SumString(St As String) As Double
   Dim cl As Range
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("A1:A29")
         .Item(UCase(cl.Value)) = cl.Offset(, 1).Value
      Next cl
      For i = 1 To Len(St)
         SumString = SumString + .Item(UCase(Mid(St, i, 1)))
      Next i
   End With
End Function

But the value in A3 has an extra space which you need to remove

Hai Fluff13,

Thanks for your valuable help it is working for me. I really appreciate your effort on this request. Thanks once again.

You're welcome & thanks for the feedback