search multiple words in a cell and output what the cell contains

Have you tried incorporating an OR statement with the IF/ISNUMBER/SEARCH for each?

It would help us to help you, if you can upload sample workbook that mirror your set up.

What you are trying to do can be achieved using several methods, but exact process will depend on your data structure/workbook set up.

1. Using PowerQuery(Get & Transform) - My preferred method, good GUI, as well as preview of each intermediate steps. But may not be suitable if you need backward compatibility beyond Excel 2010/13.
2. Using VBA/UDF - May be difficult to modify, if not familiar with it. Especially if scope changes.
3. Formula - Exact construct will depend on data layout, and how the results should be structured.


To upload, use "Go Advanced" button, follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

this is so close, the problem is that it leaves a trailing comma for any of them that do not end with pear. So for your first example, it returns apple, orange,

=IF(ISNUMBER(SEARCH("apple",B2)),"apple, ","")&IF(ISNUMBER(SEARCH("cat",B2)),"cat, ","")&IF(ISNUMBER(SEARCH("dog",B2)),"dog, ","")&IF(ISNUMBER(SEARCH("orange",B2)),"orange, ","")&IF(ISNUMBER(SEARCH("pear",B2)),"pear","")

I'm still trying to work out the trailing comma issue. Though someone else may come along with a better option.

Since you have sample solution in VBA & Formula. Here's another using PowerQuery.

1. Custom M function. Paste below into blank query in Advanced Editor and name it fnWordList

Please Login or Register  to view this content.

2. Load Original into PQ and also List of words that should be extracted.

3. Right click on Original. Transform -> Lowercase.

4. Add custom column to Original and invoke fnWordList
0.JPG

5. Change data type of [Extracted] to Text. Load to sheet.

See attached sample.

I can get you rid of the trailing commas with a second step, so if you use mine from post #6, you can use this in another column...
=IF(RIGHT(C2,2)=", ",LEFT(C2,LEN(C2)-2),C2) assuming you put the formula I gave you in post #6 in cell C2.
You haven't commented on anything given you since yesterday so we're not sure you gave up or are using someone's suggestion.