Look for a word with a range and only display a row where the word is listed

Basically I have a bunch of survey data that goes from range A1:H35752, and the cells in F6:H35752 contain text data (sentences, phrases, single words). What I need to be able to do is in cell "E2" I would like to be able to type a word such as "Apples" and any row that contains at least one instance of the word "Apple or Apples" in columns F:H would remain visible while every other row would be hidden. I have included an example that I hope will clarify what I need. Thank you all for your help!

-Update: If its not possible to to combine a plural word with a singular word then I can live with that.

1) In K2 put the word "KEY"

2) In K3 put this helper formula, then copy down as far as needed:
=ISNUMBER(MATCH("*" & $E$2 & "*", F6:H6, 0))

3) Right-click on the tab name and select VIEW CODE to open the VBA editor

4) Paste in this event macro to the sheet module that appears:

Please Login or Register  to view this content.

Read through the code until it starts to become obvious what each line of code is doing. We are essentially applying a new Autofilter to column K anytime cell E2 is edited, the Autofilter is showing all the rows that currently show "TRUE" in that column, meaning the key word was found on that row.

Now that the macro is installed and functioning, you could hide column K if you wish to make what is happening look like magic.

JBeaucaire, the thing I'm wondering about is pineapples. I couldn't figure out a way to exclude it. In the original post on the after sheet it was not included so I assumed the OP wanted to exclude it.

When faced with partial string matches, there's little chance, IMO, that Excel can be expected to know which substrings to match and which to ignore. So Apple would absolutely match to:

Apple
Apples
Crabapples
Pineapples
Grapple

Substring match is a substring match.