Differentiate Alphabets to Numbers

Dear Friends,

Please help me on these new problems. It seems the deeper I go, the greater, the problem are. But I like it cause newer things are learned. For this problem, I surrender. I need help from the great head’s.Thank You.

Numbers 7 - Numbers 4 - - - Numbers 2
4 7 2 3 - 4 C 0 B - 1 9 C 1
7 Overall - 9 Overall - - - 5 Overall




The 4 digits numbers (some combined with alphabets) in red colour are the one I put in the cells. The upper part (which I stated as numbers) are where I’m going to count all the numbers (only). The lower part (which I stated as overall) is where I’m sum up all the numbers and alphabets.
Alphabets stands for :- A= 1, B= 2, C= 3, D= 4, E= 5, F= 6.

Example:

For counting the numbers we’ll use Mr.Daddylonglegs formula
=MOD(SUM(A1:D1)-1,9)+1

Frist 4 digits : 4723 = 4+7+2+3=16=1+6=7 (since there is no alphabets in this set, the number cell and the overall cell will be same)

Second 4 digits : 4C0B = 4+0=4 (in the number cell), 4+C(3)+0+B(2) = 9
(in the overall cell)

Third 4 digits : 19C1 = 1+9+1= 11= 1+1= 2 (in the number cell),
1+9+C(3)+1= 14= 1+4= 5 (in the overall cell)

Question: It seems as I need to enter 3 formulas in one cell. How do I differentiate or give orders to the alphabet into the numbers? It’s very confusing and demanding to do it in excel format. But if I gonna do it in a piece of paper (writing) it’s very easy. Since I craft for the auto mode,,, I need help, PLEASE.

While someone figure out how to put in one cell her is solution with helper column:

(Edit: only for 4 characters words)

in red colour

cant see any red