Extract multiple keywords from text string

[HughT]

I have searched the forum but I can't find anything that quite matches this.

This is for a healthy eating project. Students record their meals in a daily diary. This is random unformatted text in a single cell (B1, B2 etc). There is a keyword list of healthy foods, each item being in a separate cell. What I would like to do is search the random text for occurrences of the keywords and return the keywords in another cell adjacent to the text cell. I would then like to be able to search the returned cells by the keyword list.

So:

Keywords (each in a separate cell, but doesn't have to be in Column A):

A1 Apple
A2 Fries
A3 Salad
A4 Burger
etc

Text (in B1)
Today I ate a burger with fries, and had an apple afterwards.

Result (in C1)
Apple Fries Burger [order is not important]

C1 to C20 (etc) will be the searchable data. I want to be able to search this by each keyword in the range A1:A4, ie 'Apple', 'Fries', 'Salad' etc so I can see who has been eating Apples, Fries, etc. Using column filters will display the contents of every cell, so if some comedian enters the whole range A1:A4 (which will actually be much larger) the filter will also return the whole range, so I need an alternative method.

I possible I would like to do this by a formula rather than VBA as I have to hand this over to someone who will not understand VBA, and can add to or alter the contents of the lookup range (A1:A4) simply by adding to it or overtyping the existing contents.

Thank you for your help.

 

[iazeemkhan]

Dear Sir,

At the outset, feel great to see your message that your fine. Have a great time. Further, Many many thanks for your updated code, its working with the sample data perfectly, superbly, you are really Genius Sir. I will check with the main data and update you Sir.

Great to have you on Mr. Excel. Once again many thanks Sir. I remain.

Best regards,

 

[Jborg]

In C1 =TEXTJOIN(" ",,IFERROR(MID(B1,IFERROR(SEARCH(A1:A4,B1,1),0),LEN($A$1:$A$4)),""))

 

[Peter_SSs]

In C1 =TEXTJOIN(" ",,IFERROR(MID(B1,IFERROR(SEARCH(A1:A4,B1,1),0),LEN($A$1:$A$4)),""))

Hi John, hope you are well.

Are you answering the original question from 2015 or the current take on it which started at post 32?

 

[Jborg]

Hi Pete, hope you are all well. I was answering the original post.

 

[Peter_SSs]

I was answering the original post.

OK. I guess TEXTJOIN didn't exist back then so a good idea to include the possibility for current readers of the thread. So in that case I make the following comments.

1. You didn't mention that your suggested formula needs to be confirmed with Ctrl+Shift+Enter, not just Enter.

2. It also suffers the same issue I raised in post 7 of the thread and demonstrated in cell C2 below where it returns "apple" when that 'word' does not appear in B2.

3. I have suggested an alternative TEXTJOIN function (also requiring Ctrl+Shift+Enter confirmation) in column C. My function also has some drawbacks related to punctuation. I have dealt with "." and "," in the column B text but if there could be other punctuation (eg ?;: ) then further SUBSTITUTE functions would be needed.

Excel Workbook
	A	B	C	D
1	Apple	Today I ate a burger with fries, and had an apple afterwards.	apple fries burger	Apple Fries Burger
2	Fries	I like pineapples & salad	apple salad	Salad
3	Salad
4	Burger
Extract