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,

Getting problem, whereevr there is Bracket in Item i.e. "Active component - Security System (CCTV & ACS)". its not displaying in Result Column. When I remove bracket from Item name than its showing the result column.

Please advice Sir.

Thanking you.

 

[Peter_SSs]

Let me put a clarification question to you.

If we had in Sheet2 a column that had just a component name and just one item name as follows

Access Control
Door Controller


Then in Sheet1 we had a cell in column A with description

HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, outdoor controller, couplers


Would you expect the result in Sheet1 to be Access Control when "door" is a whole word in Sheet2 but only part of the longer word "outdoor" in Sheet1?

 

[iazeemkhan]

Dear Sir,


The answer to your query is NO. it should read the exact item. (Which will be there in Description for different Components)

Actually Sir, what I need is that, whatever item mentioned under any Components i.e. Access Control below;

Anyone of them (ITEM) exactly will be there in Description, so need to search anyone Exact Item in Description and return its Component Name.

Access Control
[TABLE="width: 283"]
<tbody>[TR]
[TD="align: left"]Access control panel[/TD]
[/TR]
[TR]
[TD="align: left"]Card Reader[/TD]
[/TR]
[TR]
[TD="align: left"]Push button for exit[/TD]
[/TR]
[TR]
[TD="align: left"]Door contact/holder[/TD]
[/TR]
[TR]
[TD="align: left"]Electro magnetic door lock and door contact[/TD]
[/TR]
[TR]
[TD="align: left"]Mortise Lock[/TD]
[/TR]
[TR]
[TD="align: left"]Input Module[/TD]
[/TR]
[TR]
[TD="align: left"]Door controller[/TD]
[/TR]
[TR]
[TD="align: left"]Breakglass[/TD]
[/TR]
[TR]
[TD="align: left"]Intrusion Detection[/TD]
[/TR]
[TR]
[TD="align: left"]Gate barrier system[/TD]
[/TR]
[TR]
[TD="align: left"]Door position switch[/TD]
[/TR]
[TR]
[TD="align: left"]ACS Server[/TD]
[/TR]
[TR]
[TD="align: left"]Guard Tour System[/TD]
[/TR]
</tbody>[/TABLE]

Thanking you Sir,

 

[Peter_SSs]

The answer to your query is NO. it should read the exact item.

Ok, so it has to look for whole words, not part words. I think that is the problem of why your "Active component - Security System (CCTV & ACS)" is not showing up. Typically a word is preceded by a space (or nothing if it is the first word in the cell) and it is followed by a space (or nothing if it is the last word in the cell).

If we look at some of your post 49 sample data that you asked me to look at, see below, the "Active" in "Active component - Security System (CCTV & ACS)" is not preceded by a space and it is not the first word in the cell. Therefore it is seen as part of the longer "word" of "MDB-P-GF-05(1620.2kW)Active"

So the question becomes: How do we tell Excel to disregard some "part words", but not others? Or are yo able to influence how the data is obtained to ensure words are separated by spaces?

Sheet 1:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]RESULT (COMPONENT NAME )[/TD]
[/TR]
[TR]
[TD] LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-05(1620.2kW)Active component - Security System (CCTV & ACS)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

..

 

[iazeemkhan]

Dear Sir,

Yes, you are right but unfortunately its my mistake that it is not seperated while testing, it should have been MDB-P-GF-05(1620.2kW) "Active component - Security System (CCTV & ACS)
Basically, all the Items will be there in different Descriptions for Different Components and it will be exact matching which is mentioned in Sheet2. We have to just match them, if found in Description then return the Component name.


Thank you Sir.

 

[Peter_SSs]

.. it should have been MDB-P-GF-05(1620.2kW) "Active component - Security System (CCTV & ACS)

Well, if it is separated like that then the existing _v2 code works - at least it does for me as far as I can tell. It returns "Access control system" as the result for that cell now that I have inserted the space before Active as you have shown.

 

[iazeemkhan]

Dear Sir,

Its working for me only when I remove the brackets

Active component - Security System (CCTV & ACS)

i.e. Active component - Security System CCTV & ACS

Please advice

 

[Peter_SSs]

Its working for me only when I remove the brackets

Is that with the sample data from post 49?
If not please provide another small set of sample data where the problem occurs.

 

[iazeemkhan]

Dear sir,

Same Data as Post #49

 

[iazeemkhan]

Dear Sir,

Also request you to please let me know or please you insert the command to Clear the Result column in Sheet1 (From B2 to the End ) before running or getting Component command.

Thanking so much Sir.