Finding words within words in a very long list!

[NeilMcCauley]

Imagine A1:A300000 contains a long list of words, like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Applesauce22[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Antiapple[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Applesandpears[/TD]
[/TR]
</tbody>[/TABLE]

And I have a list of words I'd like to find within the above list, like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]pear[/TD]
[/TR]
</tbody>[/TABLE]

The list in column B might be a couple of thousand words long. In other words, I need to see if a couple of thousand words (B1:B2000) appear within about 300,000 cells (A1:A300000).

Right now, the closest I have come is using this formula: =LOOKUP(2^15,SEARCH(B$1:B$2000,A1),B$1:B$2000). However, it only returns one match (it would find 'Apple' in A4, but not notice that A4 also contained 'Pear'). The other key facts about this challenge are that words in column A aren't delimited in any way, they are hidden within single strings of text. That seems to be fine with the formula I just described, but maybe it would affect alternative solutions.

I'm guessing that Excel is not actually the best way to do this kind of task, because of the number of computations needed. However, I'm not technical enough to use Python or R or something. I was hoping somebody might be able to suggest a formula or module that could handle this tricky task. Perhaps the end result for the above example would be something like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A (Long list)[/TD]
[TD]Column B (First word found)[/TD]
[TD]Column C (second word found)[/TD]
[TD]Lookup words (Words I'm looking for)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Applesauce22[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bananas[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Antiapple[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Applesandpears[/TD]
[TD]Apple[/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Many thanks!

P.S. I'm using Excel 2013, on Windows 64-bit.

 

[bill0145]

Are the words always at the left or right or can they be sandwiched in the middle?

 

[NeilMcCauley]

They could be anywhere. Imagine the words I'm looking through are passwords and I want to find any that contain the word 'apple'. I need to find and mark ones that are like 'Iloveapples32' or 'Apple' or 'Apple123'. So yeah, there is no set format. And if the password/phrase contains more than one word from my lookup table, it should be marked with each word found (if possible!).

 

[NeilMcCauley]

One thing I noticed with this formula =LOOKUP(2^15,SEARCH(B$2:B$10000,A4),B$2:B$10000) [which does the job but only finds the first match], is that when you expand the range to include 10,000 cells as opposed to just a few, it outputs ?????? instead of the usual #N/A or the matched word. Perhaps this type of formula is not equipped to do this kind of search across such a massive range?