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:

[TABLE="class: grid, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A "String"[/TD]
[TD="align: center"]B "Solution"[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Cat Dog Bird[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Bird Lizard[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Cat Dog[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

​

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
String	#9 formula	different version	Word	Value	#11 formula
Cat Cat	Cat
Dog	Dog
Bird Lizard	Bird
Cat Dog	Lizard

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1​

Array Formulas Cell <tbody> [TH="align: left"]Formula[/TH] [TH="bgcolor: #DAE7F5"]B2[/TH] [TD="align: left"]{=SUM( ((LEN(" "&A2&" ")-LEN(SUBSTITUTE(" "&A2&" "," "&$D$2:$D$5&" ","")))/LEN(" "&$D$2:$D$5&" ")*$E$2:$E$5) )}

<tbody>

[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=SUM( ((LEN(" "&A2&" ")-LEN(SUBSTITUTE(" "&A2&" ",$D$2:$D$5&" ","")))/LEN($D$2:$D$5&" ")*$E$2:$E$5) )}[/TD]

[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=SUM( ((LEN(" "&SUBSTITUTE(A2," "," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(A2," "," ")&" "," "&$D$2:$D$5&" ","")))/LEN(" "&$D$2:$D$5&" ")*$E$2:$E$5) )}[/TD]

</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself [/TD]

</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.