EXCEL VBA - search for all keywords mentioned in phrase

[petrcz]

Hello,
I have this set of data:

	A	B	C
1	Phrases	Keywords	Result
2	cruise from USA to Cuba	USA	cruise, USA, Cuba
3	luxury cruise Cuba	Cuba	cruise, Cuba
4	holiday Cuba	Cruise	Cuba

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I need function which does this:
- add in column C all keywords from column B which are found in column A. Separate this keywords in column C by comma.

I have this function: =IF(ISNUMBER(SEARCH($B$2;OFFSET(A2;0;0)));$B$2;"")
But do not know, hot to loop it.
I need to check each row in column A againts all keywords in column B.

Thank you in advance for any help.
PR

 

[Eric W]

If you have the new TEXTJOIN function that Microsoft added in 2016, then put this formula in C2:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH($B$2:$B$4,A2)),$B$2:$B$4,""))

change the ranges to match your sheet, confirm with Control+Shift+Enter, then drag down the column.

If you don't have TEXTJOIN, then you can either use several work columns to the right (D, E, F, theoretically as many as you have words in column B), or you can use some version of VBA.

Let me know what works for you.

 

[petrcz]

Hello Eric,
thank you for your reply.
Best
Petr