how to check if a list of keywords are present as substring/substrings within a cell please? and rep-CodePudding

How do I search if an Excel cell contains word or words listed in a separate column, please? If a substring is present, then show what that matching substring is. If a second substring is met, then show that in the next column.

For example

Column A with raw data has these rows:

There is a dog A
Cat is happy
Cat and dog are both animals
Elephant is big
Happy cAt, dOg, fox, MOUSE Elephant are good
Hello dogelephantMOouse Hello Hello

Column B contains has the following ordered keys (in decreasing order of importance):

Row 1: dog
Row 2: cat
Row 3: mouse

Desired Columns C, D, E ... are as following:

I do not have XLOOK function. I think the functions to use may be a combination of SEARCH, MATCH, COUNTIF, VLOOK. But I do not know exactly how to write it up.

QUESTION: How do I check if a list of keywords are present as a substring/substrings in a cell, please? (columns D, E, F .. shown).

The keywords have level of important (for example dog and cat are both present in one cell (cell A4), but column B shows dog is more important; therefore cell C4 reports dog first, then D4 reports cat next; even though in cell A4 cat occurred before dog).

Thank you

PS: I use offline Desktop version Microsoft Excel 2019 on Windows 10 (64 bit).

ADDENDUM:

This is the output I would like:

Showing the actual matched substrings down along the row to the right. First match is placed in column C, next match in column D so on.

CodePudding user response：

What do you imagine the output looking like? If you want to only use excel functions (ie no macros) you could do something like mapping the values to a number and adding them up:

In this i'd put your lookup table in a8:b10, and this was in b2

=IFERROR(IF(FIND($A$8,A2),$B$8,0),0) IFERROR(IF(FIND($A$9,A2),$B$9,0),0) IFERROR(IF(FIND($A$10,A2),$B$10,0),0)

I've mapped them to values so b2 tells you dog, b3 tells you cat, and b4 implies dog cat

I suspect this isn't the form of output you want so maybe draw up what you want it to look like manually and i'll see if I can help further/edit this?

CodePudding user response：

You can use this formula in C2 (if you have Excel 365 current channel):

=LET(keys,TRANSPOSE($B$2:$B$4),
     result,MAP(keys,LAMBDA(k,IF(ISNUMBER(SEARCH(k,A2)),k,""))),
     FILTER(result,result<>"",""))

It transposes the keys to columns.

And then MAP checks per cell if the keyword is present in A2 - if not, keyword is removed.

To have a clean result - FILTER removes the empty results.

BTW: in your last row (Hello dogelephantMOouse Hello Hello) there is no mouse - but a typo-mouse: MOouse.