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)

 

[Peter_SSs]

I would be surprised if you get a successful formula using standard worksheet functions. Even your existing formula can return incorrect results. Try it with the sample column A fruits you have given and these two values in column C:
I like pineapple
Nothing appears here

Secondly, I assume that "plum" is an error in both places you have given expected results since that fruit does not appear in column A.

You could test this user-defined function.

Function ListFruit(rFruitItems As Range, sText As String) As String
  Dim d As Object, RX As Object, M As Object

  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "\b(" & Join(Application.Transpose(rFruitItems.Value), "|") & ")\b"
  For Each M In RX.Execute(sText)
    d(CStr(M)) = 1
  Next M
ListFruit = Join(d.keys(), vbLf)
End Function

Column E would need to be set to 'Wrap Text'

Dave87 2020-07-02 1.xlsm
	A	B	C	D	E
1	Lookup Items		Within Text
2	orange		Today I ate 2 apple and 3 orange. I like apple		apple orange
3	apple		tomorrow I will juice 1 pear		pear
4	strawberry
5	pear		this could be blank.
6	Kiwi		I need to buy some apple, orange, kiwi and plum		apple orange kiwi
List Fruit

Cell Formulas
Range	Formula
E2:E6	E2	=ListFruit(A$2:A$6,C2)

 

[QuestionBaker]

I am trying the same thing without macros, and I am half way there...

{=SUMPRODUCT(IF(ISNUMBER(SEARCH($A$1:$A$5,C1,1)),1,0),ROW($A$1:$A$5))}

I am able to find out which fruits have matches, but I am unable to display the text,

 

[Peter_SSs]

I am half way there...

{=SUMPRODUCT(IF(ISNUMBER(SEARCH($A$1:$A$5,C1,1)),1,0),ROW($A$1:$A$5))}

I am able to find out which fruits have matches, but I am unable to display the text,

I'm sorry, but I do not think you are even half way there. Consider this example with your formula. It is showing a match for both apple and pear, neither of which words actually occur in C4

 

[QuestionBaker]

Yes, you are correct, it gives a false positive...
a very cheap solution that I found is to add space before the words in column A (this will count words like "apple,", "apples", "apple.")
But as you pointed out with the earlier solution, it isn't a robust solution (Sam as a hit for Samson)...

 

[Peter_SSs]

a very cheap solution that I found is to add space before the words in column A

You would have to add a space after the words as well as before. And that still does not resolve the issue of how to actually extract & list the words

 

[Dave87]

@Peter_SSs thank you very much, your solution in post #2 worked perfect (& yes it was an error to list plum).

@QuestionBaker thanks for your efforts

 

[Peter_SSs]

Glad it worked for you. Thanks for the follow-up.

One thing that I forgot to mention: The list in column A should not contain blank cells. If that was possible, then I would modify the function to solve the resulting problem. Post back if that is required.

 

[Dave87]

One thing that I forgot to mention: The list in column A should not contain blank cells. If that was possible, then I would modify the function to solve the resulting problem. Post back if that is required.

No blank cells so its working perfect. thank you very much

 

[Peter_SSs]