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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]text WW text text text[/td][td="bgcolor:#CCFFCC"]WW[/td][td="bgcolor:#CCFFCC"]B2: =LOOKUP(9999, SEARCH({"WW","XX","YY","ZZ"}, A2), {"WW","XX","YY","ZZ"})[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]text text XX text text[/td][td="bgcolor:#CCFFCC"]XX[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]YY text text text[/td][td="bgcolor:#CCFFCC"]YY[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]text text text ZZ[/td][td="bgcolor:#CCFFCC"]ZZ[/td][td][/td][/tr]
[/table]

 

[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
text WW text text text	WW	WW
text text XX text text	XX	XX
YY text text text	YY	YY
text text text ZZ	ZZ	ZZ

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

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

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

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

</tbody>

Sheet350​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=LOOKUP(2,1/SEARCH(N$2:N$5,L2),N$2:N$5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]text WW text text text[/TD]
[TD="bgcolor: #CCFFCC"]WW[/TD]
[TD="bgcolor: #CCFFCC"]B2: =LOOKUP(9999, SEARCH({"WW","XX","YY","ZZ"}, A2), {"WW","XX","YY","ZZ"})[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]text text XX text text[/TD]
[TD="bgcolor: #CCFFCC"]XX[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]YY text text text[/TD]
[TD="bgcolor: #CCFFCC"]YY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]text text text ZZ[/TD]
[TD="bgcolor: #CCFFCC"]ZZ[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.