summing numerical value of alphabets in a word

[asad]

Is it possible to add the numerical value of alphabets in a word in one cell?
I will explain with an example.
If I type in Asad in A1, B1 should add up like this : A+S+A+D and the total should come up to 25 (1+19+1+4).
And if I want to allocate different numerical values to alphabets, will it be different formula? For examlple, if I want A to be 10, S to be 50 and D to be 15, the answer should be 85.

Asad

 

[shg]

How about,

=SUM(CODE(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1)) - 64)

... confirmed with CSE.

Asad returns 121, not 175 ...

 

[barry houdini]

Hello shg,

I think it's 175 given the values shown in Asad's table. The reason that you get 250 for Asad or ASAD is that Aladin's suggested formula is not case-sensitive so for each letter you get both the upper and lower case values added.

This version of Aladin's formula will distinguish between cases


=SUM(EXACT(Letters,TRANSPOSE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))*Values)

This one needs to be confirmed with CTRL+SHIFT+ENTER

 

[asad]

Asad returns 121, not 175 ...

shg again to my aid .
You are right if I don't assign any values to alphabets. If you look at my previous post, I have assigned different values to small letters. They will be like this

1+69+51+54

How can I get that to work?

Asad

 

[asad]

Hello shg,

I think it's 175 given the values shown in Asad's table. The reason that you get 250 for Asad or ASAD is that Aladin's suggested formula is not case-sensitive so for each letter you get both the upper and lower case values added.

This version of Aladin's formula will distinguish between cases


=SUM(EXACT(Letters,TRANSPOSE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))*Values)

This one needs to be confirmed with CTRL+SHIFT+ENTER

Thanks a lot Barry. It does work like a magic.

Asad.

 

[shg]

I think it's 175 given the values shown in Asad's table.

Sorry, mised that.

Could lose the transpose, could you not?

=SUM(EXACT(letters, MID(A1, COLUMN(INDIRECT("1:" & LEN(A1))), 1)) * values)

 

[Aladin Akyurek]

Sorry, mised that.

Could lose the transpose, could you not?

=SUM(EXACT(letters, MID(A1, COLUMN(INDIRECT("1:" & LEN(A1))), 1)) * values)

Would be interesting to compare this speedwise with:

=SUM(EXACT(LETTERS,TRANSPOSE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))*VALUES)

Also, compare the latter with:

=SUM(IF(EXACT(LETTERS,TRANSPOSE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),VALUES))

The Transpose version could be faster; and, the If version should be faster than the pairwise multiplication version.

 

[asad]

Wow, all these solutions for me.........................

I am very thankful to all of you guys - Aladin, Barry, and shg. All of you have been very helpful. Thanks again.

Asad.