extract text from a sentence (in a cell) based on a list

[ExcelNewbie2020]

Sirs,

How can I extract text based on the list. see below
I would like to extract the animal name in the sentence and put it in the other cell.. thanks

description	formula result			animal list
the quick brown fox jump over the lazy dog	fox, dog			fox
the goat and a chicken try to cross a weak and narrow bridge across the river	goat, chicken			dog
				goat
				chicken

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	Description	Formula Result			Animal List
2	The quick brown fox jumps over the lazy dog	fox, dog			fox
3	The goat and a chicken try to cross a weak and narrow bridge across the river	goat, chicken			dog
4					goat
5					chicken
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH($E$2:$E$5,A2)),$E$2:$E$5,""))

 

[ExcelNewbie2020]

Try:

Book1
	A	B	C	D	E
1	Description	Formula Result			Animal List
2	The quick brown fox jumps over the lazy dog	fox, dog			fox
3	The goat and a chicken try to cross a weak and narrow bridge across the river	goat, chicken			dog
4					goat
5					chicken
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH($E$2:$E$5,A2)),$E$2:$E$5,""))

thanks bro, it worx..

 

[gelu]

For some reason TEXTJOIN (such an elegant solution) does not work the same way on my system (Excel 2019).

text from sentence.xlsx
	A	B	C	D
1
2	the quick brown fox jump over the lazy dog	fox, dog, goat, chicken		fox
3
4	the goat and a chicken try to cross a weak and narrow bridge across the river	goat, chicken		dog
5	the quick brown fox jump over the lazy dog	fox, dog		goat
6				chicken
7
8	the quick brown fox jump over the lazy dog			fox
9	the goat and a chicken try to cross a weak and narrow bridge across the river			dog
10				goat
11				chicken
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH(D2:D6,A2)),D2:D6,""))
B4:B5	B4	=SUBSTITUTE(IFERROR(MID($A4,FIND($D$2,$A4,1),LEN($D$2)),"")&", "&IFERROR(MID($A4,FIND($D$4,$A4,1),LEN($D$4)),"")&", "&IFERROR(MID($A4,FIND($D$5,$A4,1),LEN($D$5)),"")&", "&IFERROR(MID($A4,FIND($D$6,$A4,1),LEN($D$6)),""),CHAR(44)&CHAR(32)&CHAR(44)&CHAR(32),"")
B8	B8	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH($D$2:$D$6,A8)),$D$2:$D$6,""))
B9	B9	=TEXTJOIN(", ",1,IF(ISNUMBER(SEARCH($D$8:$D$11,A9)),$D$8:$D$11,""))

thanks bro, it worx..

 

[Eric W]

For some reason TEXTJOIN (such an elegant solution) does not work the same way on my system (Excel 2019).

The TEXTJOIN should work on your system too. The difference is that on 2019, you need to array enter the formula. When you enter the formula, use Control+Shift+Enter instead of just Enter.