I'm trying to find a way to add up the numerical value of a letters.

If I enter 'a' into cell A1, I would like cell B2 to show the number '1'

That way I can add up the numerical value of words.

Thanks!

=CODE(LOWER(A1))-96


"Raza" wrote:

I'm trying to find a way to add up the numerical value of a letters.

If I enter 'a' into cell A1, I would like cell B2 to show the number '1'

That way I can add up the numerical value of words.

Thanks!

I'm trying to find a way to add up the numerical value of a letters.

If I enter 'a' into cell A1, I would like cell B2 to show the number '1'

That way I can add up the numerical value of words.

If you type only letters and/or spaces into A1 (and no other characters
including punctuation marks), and assuming you don't want to count the
spaces in your total, then this formula will add up the values of the
letters in A1...

=SUMPRODUCT((CODE(MID(LOWER(A1),ROW(INDIRECT("A1:A "&LEN(A1))),1))-96)*(MID(LOWER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1 )<"
"))

where upper and lower case letters are considered as being the same. So, if
you type in "Hello There" (without the quote marks, just the letters and the
space), the above formula will return 108. If you need other characters
besides letters and spaces, let us know and we might be able to modify the
formula to account for them (make sure you tell us how you want them handled
though).

Rick

Actually, this modified formula will sum ONLY the letter values, no matter
what other characters you mix into the text.

=SUMPRODUCT((CODE(MID(LOWER(A1),ROW(INDIRECT("A1:A "&LEN(A1))),1))-96)*(NOT(ISERROR(FIND(MID(LOWER(A1),ROW(INDIRECT(" A1:A"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz"))) ))

So, the text "a.b,c?d=e" will sum to 15 which is the sum of a=1, b=2, c=3,
d=4, e=5, etc.

Now, I'll let the people here who really know how to construct formulas
figure out how to trim this one down to a more reasonable size.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm trying to find a way to add up the numerical value of a letters.

If I enter 'a' into cell A1, I would like cell B2 to show the number '1'

That way I can add up the numerical value of words.

If you type only letters and/or spaces into A1 (and no other characters
including punctuation marks), and assuming you don't want to count the
spaces in your total, then this formula will add up the values of the
letters in A1...

=SUMPRODUCT((CODE(MID(LOWER(A1),ROW(INDIRECT("A1:A "&LEN(A1))),1))-96)*(MID(LOWER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1 )<"
"))

where upper and lower case letters are considered as being the same. So,
if you type in "Hello There" (without the quote marks, just the letters
and the space), the above formula will return 108. If you need other
characters besides letters and spaces, let us know and we might be able to
modify the formula to account for them (make sure you tell us how you want
them handled though).

Rick

I don't have any fantastic suggestions, but you could eliminate one function
call by replacing NOT(ISERROR(FIND(..))) with ISNUMBER(FIND(..))


"Rick Rothstein (MVP - VB)" wrote:

Actually, this modified formula will sum ONLY the letter values, no matter
what other characters you mix into the text.

=SUMPRODUCT((CODE(MID(LOWER(A1),ROW(INDIRECT("A1:A "&LEN(A1))),1))-96)*(NOT(ISERROR(FIND(MID(LOWER(A1),ROW(INDIRECT(" A1:A"&LEN(A1))),1),"abcdefghijklmnopqrstuvwxyz"))) ))

So, the text "a.b,c?d=e" will sum to 15 which is the sum of a=1, b=2, c=3,
d=4, e=5, etc.

Now, I'll let the people here who really know how to construct formulas
figure out how to trim this one down to a more reasonable size.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm trying to find a way to add up the numerical value of a letters.

If I enter 'a' into cell A1, I would like cell B2 to show the number '1'

That way I can add up the numerical value of words.

If you type only letters and/or spaces into A1 (and no other characters
including punctuation marks), and assuming you don't want to count the
spaces in your total, then this formula will add up the values of the
letters in A1...

=SUMPRODUCT((CODE(MID(LOWER(A1),ROW(INDIRECT("A1:A "&LEN(A1))),1))-96)*(MID(LOWER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1 )<"
"))

where upper and lower case letters are considered as being the same. So,
if you type in "Hello There" (without the quote marks, just the letters
and the space), the above formula will return 108. If you need other
characters besides letters and spaces, let us know and we might be able to
modify the formula to account for them (make sure you tell us how you want
them handled though).

Rick