Assign values to case sensitive characters within string

[tbsweet52]

Hi,

I've been stumped on this all day.

I have a table set up with values assigned to the uppercase and lowercase version of each letter in the alphabet.

I basically want to take a sentence, match the letters, and output the sum of their values.

Example:

a	5
b	3
c	2
A	6
B	7
C	5
" " (space between words)	2

Then the string "ABC abc Abc" would equal (6+7+5+2)+(5+3+2+2)+(6+3+2) = 43

Any help would be greatly appreciated.

Thanks in advance!

[mehmetcik]

This screams User Defined Function.

anything else would get too complicated.

Give me a few minutes.


OK

This is my first Pass. It is a macro that proves the method can work. It is easier to test than a UDF.

Please Login or Register  to view this content.

And Finally your UDF is ready.

Please Login or Register  to view this content.

The attached Spreadsheet uses the UDF in cell D2.

[Tony Valko]

This works on your limited example!

Create this table:

Data Range

	A	B	C
9	Character	Code	Value
10	a	97	5
11	b	98	3
12	c	99	2
13	A	65	6
14	B	66	7
15	C	67	5
16		32	2

Enter a plain space character in A16.

Enter this formula in B10 and copy down to B16:

=CODE(A10)

Then...

Data Range

	A	B	C
1	ABC abc Abc	43
2
3
4
5
6
7
8
9	Character	Code	Value
10	a	97	5
11	b	98	3
12	c	99	2
13	A	65	6
14	B	66	7
15	C	67	5
16		32	2

Enter this formula in B1:

=SUMPRODUCT(SUMIF(B10:B16,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),C10:C16))

[tbsweet52]

Please Login or Register  to view this content.

That works perfect, thanks!

[tbsweet52]

Enter this formula in B1:

Please Login or Register  to view this content.

This also works perfect, thanks!

[Tony Valko]

You're welcome. Thanks for the feedback!