Numerical Value of letters in words

Okay, I know this is strange, but here's my thought and request:
In the English alphabet, we have 26 letters. Assuming A=1, B=2, C=3....Z=26, how would I construct a formula (or a UDF in VBA) to determine the numerical value of the contents of a cell?

For example, if the cell contained "Hello" we would have 8 for H, 5 for E, 12 each for the L's, and 15 for the O.
Adding all of those together, the "value" of the cell would be 52.

If the cell contained "Hello 7" then the "value" would be 52+7, or 59.
We could also assign other constants, like a space = 0, a comma counts as 1, a period as 2, other punctuation = 0.

So, if I had a column of cells, how would I determine the "value" of each cell in that column?

Any bright ideas would be very welcome.

Please see yellow banner at top page on how to attach a sample workbook, including a complete of characters and their respect values.

Sure, sample workbook attached.

Formula:

Please Login or Register  to view this content.

"Hello 7" then the "value" would be 52+7

if digets must be 1-1,2-2 ....9-9 then add it in to the list
another
array formula

Formula:

Please Login or Register  to view this content.

BMV,
Both of your formulas work for the first scenario, and the second one seems to work well for "Hello: 7" so far. Thanks for both. I think I have a chance of understanding the first formula, but the second one scares me to death.

Fluff,
Not sure if it's a typo, but I can't get your formula to work because I don't know what the "co" is in your formula, and neither does Excel. Care to explain?

Yep, even though I'm on Office 365, our IT department keeps us many version behind, so I don't have the LET function yet.

Please try
=SUM(IFERROR(--MID(E3,SEQUENCE(99),1),IFERROR(MOD(TEXT(SEARCH(MID(E3,SEQUENCE(99),1)," ABCDEFGHIJKLMNOPQRSTUVWXYZ12,.:;"),"[>32]32;0"),28)-1,)))

In G10 then copied down.

Formula:

Please Login or Register  to view this content.

For 365 version ARRAY entry not required.