Assigning No. to alphabets

[avinashraste]

Hello,

Dear all, can I assign a no. to an alphabet which will return the same in an another cell ?? For eg. If I have decided to give 1 to A, 2 to B & 3 to C. Now if I write abc in cell A1, it will give me the total of abc ( 1+2+3 = 6) in cell B1. Is this possible ? Please guide & help.

Thanks & Regards
Avinash Raste

 

[Ruddles]

How long is A1 likely to get? Always three characters? More?

Have you ever used VBA?

 

[njimack]

=code(left(a1))+code(mid(a1,2,1))+code(right(a1))-(96*3)

 

[avinashraste]

Dear Ruddles,

Thank you very much for replying. Well, A1 might contain more than 3 charecters, 5,7 might be 20...I don't know. Do u think the length might make it difficult to work ? And yes, I'm not aware about vba, I mean I have never used it & i do not know how to use & what to write & where to write.

Thanks & Regards

Avinash Raste

 

[avinashraste]

Dear njimack,

Thank you very much for your reply & guidence. Its working very fine, but if I put a long string, it shows the figure 31 in B2 and if I put Capital letter it shows the negative value.

Thanks & Regards

Avinash Raste

 

[PA HS Teacher]

Try
=SUMPRODUCT(CODE(UPPER(MID(A1,ROW(H$1:INDEX(A:A,LEN(A1))),1)))-64)

Can be any length. Treats upper and lower case letters the same. Assumes A=1, B=2 etc.

 

[avinashraste]

Dear PA HS Teacher,

Thank you very much for this fantastic solution. It is working very fine. This is exectly what I wanted. And let me tell you sorry for delay in replying. Due to heavy rain we had an electricity cut.

just one thing..if a string has a space, the count changes. Well, that i can handle by avoiding space.

And thank you very much once again for providing the solution and help.

Thanks & Regards

Avinash Raste

 

[PA HS Teacher]

Instead of A1

Use,
=SUBSTITUTE(A1," ","")

either in a 'helper' cell before using the previous suggestion, or replace A1 with the Substitute(...) directly in the formula.

You can nest Substitute functions to ignore any undesired characters as well.

 

[avinashraste]

Dear PA HS Teacher,

Just perfect. You solved all the problems. Thank you very much. You are really great in MS Excel.

Thanks & Regards

Avinash Raste