Assign Value to each word in a string

[HockeyDiablo]

I am looking to assign a value to words, then total the value. I am only coming up with solutions that add one value and not all of them.

Cat=1
Dog=2
Bird=3
Lizard=4

Example:

	A "String"	B "Solution"
1	Cat Dog Bird	6
2	Dog	2
3	Bird Lizard	7
4	Cat Dog	3

<tbody>
</tbody>

​

Thank you

 

[Eric W]

Darn, even the improved version has some issues. Consider the string "Cat Cat". I modified the modified version to:

{=SUM(((LEN(" "&SUBSTITUTE(A2," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(A2," "," ")&" "," "&$D$2:$D$5&" ","")))/LEN(" "&$D$2:$D$5&" ")*$E$2:$E$5))}

and it seems to handle that case. Even so, I think JoeMo's UDF in #6 is probably the best choice. No worries about non-numeric data in E2:E5 for example.

 

[Rick Rothstein]

Darn, even the improved version has some issues. Consider the string "Cat Cat.

What do you see that was wrong with the "improved" formula I posted in Message #9 with respect to "Cat Cat"? The value of Cat was 1 and "Cat Cat" evaluates to 2, 1 for each appearance of the word... isn't that what you would expect it to do?

 

[Eric W]

Right, I expect to see 2, but when I put the #9 formula in B2 I get 1:

	A	B	C	D	E	F
1	String	#9 formula	different version	Word	Value	#11 formula
2	Cat Cat	1	2	Cat	1	2
3	Dog	2	2	Dog	2	2
4	Bird Lizard	7	7	Bird	3	7
5	Cat Dog	3	3	Lizard	4	3

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2 {=SUM(((LEN(" "&A2&" ")-LEN(SUBSTITUTE(" "&A2&" "," "&$D$2:$D$5&" ","")))/LEN(" "&$D$2:$D$5&" ")*$E$2:$E$5))} C2 {=SUM(((LEN(" "&A2&" ")-LEN(SUBSTITUTE(" "&A2&" ",$D$2:$D$5&" ","")))/LEN($D$2:$D$5&" ")*$E$2:$E$5))} F2 {=SUM(((LEN(" "&SUBSTITUTE(A2," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(A2," "," ")&" "," "&$D$2:$D$5&" ","")))/LEN(" "&$D$2:$D$5&" ")*$E$2:$E$5))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If I only append a space to the end of the D2:D5 words, I get the formula in C2 and I get a result of 2. And F2 shows the formula I proposed in post #11.