find different words, and act different by finding them

[danielhollander]

Hi!


My problem is the following.


In cell L2 is a sentence that can contain 4 different words. The function first need to find the word, then act on it.


As exampel:


text WW text text text
text text XX text text
YY text text text
text text text ZZ


When cell l2 contains WW, cell M2 need to say WW
When cell l2 contains XX, cell M2 need to say XX
When cell l2 contains YY, cell M2 need to say YY
When cell l2 contains ZZ, cell M2 need to say ZZ


Hopefully someone understands, and help me.


Many thanks!

 

[shg]

	A​	B​	C​
2​	text WW text text text	WW	B2: =LOOKUP(9999, SEARCH({"WW","XX","YY","ZZ"}, A2), {"WW","XX","YY","ZZ"})
3​	text text XX text text	XX
4​	YY text text text	YY
5​	text text text ZZ	ZZ

 

[oldbrewer]

use a lookup table ?

 

[jtakw]

Hi,

Similar to shg's, just using a List/Table for the keywords:

Book1
	L	M	N
2	text WW text text text	WW	WW
3	text text XX text text	XX	XX
4	YY text text text	YY	YY
5	text text text ZZ	ZZ	ZZ
Sheet350

Cell Formulas
Range	Formula
M2	=LOOKUP(2,1/SEARCH(N$2:N$5,L2),N$2:N$5)

 

[danielhollander]

Hi,

Similar to shg's, just using a List/Table for the keywords:

	L	M	N
2	text WW text text text	WW	WW
3	text text XX text text	XX	XX
4	YY text text text	YY	YY
5	text text text ZZ	ZZ	ZZ

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet350​

Worksheet Formulas Cell Formula M2 =LOOKUP(2,1/SEARCH(N$2:N$5,L2),N$2:N$5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks the column can recognize if the words are in the sentence now. He put a 0 if those words are found.

I want the column to repeat the word he found. Do you have a solution for that?

 

[danielhollander]

	A​	B​	C​
2​	text WW text text text	WW	B2: =LOOKUP(9999, SEARCH({"WW","XX","YY","ZZ"}, A2), {"WW","XX","YY","ZZ"})
3​	text text XX text text	XX
4​	YY text text text	YY
5​	text text text ZZ	ZZ

<tbody>
</tbody>

What do you mean with "9999".
Does this lookup repeat the words found?

 

[jtakw]

Both shg and my formula returns the Word that is found, I don't know what you mean by:

He put a 0 if those words are found.