Extract multiple keywords from text string

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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.
 
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,
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
In C1 =TEXTJOIN(" ",,IFERROR(MID(B1,IFERROR(SEARCH(A1:A4,B1,1),0),LEN($A$1:$A$4)),""))
 
Upvote 0
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?
 
Upvote 0
Hi Pete, hope you are all well. I was answering the original post. :) :)
 
Upvote 0
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
ABCD
1AppleToday I ate a burger with fries, and had an apple afterwards.apple fries burgerApple Fries Burger
2FriesI like pineapples & saladapple saladSalad
3Salad
4Burger
Extract
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top