searchign for multple combination of string text

Hello

Let's say I have the following list
abc
def
ghi
jkl

And in another column I have cells (each line in one cell) that may contain a combination of the above and other strings that are not part of the list.

abc tyr eyw
aja abc tye
fhw swe ghi

and so on.

Is there a way to search inside each of the above cell for the string in the list, and return the found string?

Thanks!

With your second list starting in D1, try this array formula (enter with CNTRL SHFT ENTER rather than ENTER)

=INDEX($A$1:$A$4,MATCH(0,--EXACT(D1,SUBSTITUTE(D1,$A$1:$A$4,"")),0))
See attachment

Post an actual workbook with sample data, and a manual mockup of the desired results you're trying to achieve. This enables others to quickly assist on an actual target. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

If you are OK to place them in a separate cells,

E2, with CTRL+SHIFT+ENTER,

=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(SEARCH($A$2:$A$9,$D2)),ROW($A$2:$A$9)),COLUMNS($E2:E2))),"")

Then copy across.

If order is NOT an issue, with just ENTER in E2,

=IFERROR(LOOKUP(9.9E+300,SEARCH($A$2:$A$9,$D2)/ISNA(MATCH($A$2:$A$9,$D2:D2,0)),$A$2:$A$9),"")

Then copy across.

I would use VBA for this to add a new function into your Excel. This function would be used like any other formula, you're just installing it yourself.

Please Login or Register  to view this content.

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook (test workbook.xlsm)

The function is installed and ready to use.

============

The function is used like so in cell E2:

=UniqueWords(D2, $A$2:$A$9)

First Parameter = The cell with the string to extract words from
Second Parameter = the range of cells listing your words to remove from the string