Extract multiple specified values from cell and return in 1 cell

[Dave87]

Hello everyone,

I need some help on this, in column A i have a list of various fruit, within column C i have sentences which may contain one or more of the fruit listed in Column A.

In column E i would like to list the fruit from column A which is contained in column C.

Also i would like to remove any references to duplicate fruit - see cell E2 which only lists apple once

I'm currently using the following formula but it only returns the first 'fruit' listed in column A and not all the 'fruits' in column C

{=IFERROR(INDEX($A$2:$A$6,MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$6,C2)),0)),"")}

Column A

Lookup Items

orange

apple

strawberry

pear

Kiwi

Column C

Within Text

Today I ate 2 apple and 3 orange. I like apple

tomorrow I will juice 1 pear​

this could be blank.​

I need to buy some apple, orange, kiwi and plum

Column E - Required Answer

apple orange

pear

0

apple orange kiwi plum

I have Excel 2016 and 365/online/onedrive

(work have blocked XL2BB & Google drive so i cant share a document)

 

[Amit Tandon]

26Aug19.xlsx
	A	B	C	D	E	F
1					Option 1	Option 2
2	orange		Today I ate 2 apple and 3 orange. I like apple		orange apple	orange apple
3	apple		tomorrow I will juice 1 pear		pear	pear
4	strawberry
5	pear		I need to buy some apple, orange, kiwi and plum		orange apple kiwi	orange apple kiwi
6	kiwi		I will buy kiwi pear, apple, kiwi and kiwi and kiwi		apple pear kiwi	apple pear kiwi
Sheet49

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(TEXTJOIN(CHAR(10),,IF(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"."," "),",", " ")," ",REPT(" ",LEN(C2))),(ROW($1:$255)-1)*LEN(C2)+1,LEN(C2))),A$2:A$6,0),""),ROW($1:$255)),A$2:A$6,"")),"")
F2:F6	F2	=IFERROR(TEXTJOIN(CHAR(10),,IF(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"."," "),",", " ")," ",REPT(" ",LEN(C2))),(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1))-1)*LEN(C2)+1,LEN(C2))),A$2:A$6,0),""),ROW(INDIRECT("1:"&ROWS(A$2:A$6)))),A$2:A$6,"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Amit Tandon]

26Aug19.xlsx
	A	B	C	D	E	F
1					Option 1	Option 2
2	orange		Today I ate 2 apple and 3 orange. I like apple		orange apple	orange apple
3	apple		tomorrow I will juice 1 pear		pear	pear
4	strawberry
5	pear		I need to buy some apple, orange, kiwi and plum		orange apple kiwi	orange apple kiwi
6	kiwi		I will buy kiwi pear, apple, kiwi and kiwi and kiwi		apple pear kiwi	apple pear kiwi
Sheet49

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(TEXTJOIN(CHAR(10),,IF(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"."," "),",", " ")," ",REPT(" ",LEN(C2))),(ROW($1:$255)-1)*LEN(C2)+1,LEN(C2))),A$2:A$6,0),""),ROW($1:$255)),A$2:A$6,"")),"")
F2:F6	F2	=IFERROR(TEXTJOIN(CHAR(10),,IF(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"."," "),",", " ")," ",REPT(" ",LEN(C2))),(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1))-1)*LEN(C2)+1,LEN(C2))),A$2:A$6,0),""),ROW(INDIRECT("1:"&ROWS(A$2:A$6)))),A$2:A$6,"")),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

This is a solution using formulas. Presume that each sub-string within a column C cell is separated by a space or comma or a dot.

 

[Peter_SSs]

This is a solution using formulas. Presume that each sub-string within a column C cell is separated by a space or comma or a dot.

With the same assumptions & sample data ..

Dave87 2020-07-02 1.xlsm
	A	B	C	D	E
1					Option 3
2	orange		Today I ate 2 apple and 3 orange. I like apple		orange apple
3	apple		tomorrow I will juice 1 pear		pear
4	strawberry
5	pear		I need to buy some apple, orange, kiwi and plum		orange apple kiwi
6	kiwi		I will buy kiwi pear, apple, kiwi and kiwi and kiwi		apple pear kiwi
List Fruit (3)

Cell Formulas
Range	Formula
E2:E6	E2	=TEXTJOIN(CHAR(10),1,FILTER(A$2:A$6,ISNUMBER(SEARCH(" "&A$2:A$6&" ",SUBSTITUTE(SUBSTITUTE(" "&C2&" ","."," "),","," "))),""))

 

[Amit Tandon]

With the same assumptions & sample data ..

Dave87 2020-07-02 1.xlsm
	A	B	C	D	E
1					Option 3
2	orange		Today I ate 2 apple and 3 orange. I like apple		orange apple
3	apple		tomorrow I will juice 1 pear		pear
4	strawberry
5	pear		I need to buy some apple, orange, kiwi and plum		orange apple kiwi
6	kiwi		I will buy kiwi pear, apple, kiwi and kiwi and kiwi		apple pear kiwi
List Fruit (3)

Cell Formulas
Range	Formula
E2:E6	E2	=TEXTJOIN(CHAR(10),1,FILTER(A$2:A$6,ISNUMBER(SEARCH(" "&A$2:A$6&" ",SUBSTITUTE(SUBSTITUTE(" "&C2&" ","."," "),","," "))),""))

In my office 365 version (and many others) the new functions are still not available ... hopefully this month we should get them.

 

[Amit Tandon]

In my office 365 version (and many others) the new functions are still not available ... hopefully this month we should get them.

Without using any of these functions we can also use below Array formula (Ctrl+Shift+Enter):

=TEXTJOIN(CHAR(10),,IF(ISNUMBER(SEARCH(" "&A$2:A$6&" "," " &SUBSTITUTE(SUBSTITUTE(C2,"."," "),",", " ")&" ")),A$2:A$6,""))