Hello,

I need some help writing a function that will search for a short text string through a range of cells containing long text strings. I have a range of cells containing sentences and I have a list of words in a different book (not that it matters where the list is). I want to be able to check which words appear in any of the cells within the range.

I looked in an old thread on this site:http://www.excelforum.com/excel-form...-contains.html
It showed this function:
=--(COUNTIF(range,"*fred*")>0)
This would be fine if I only had one or two text strings to search for, but I have hundreds.
The function could easily be modified to:
=--(COUNTIF(range,A1)>0)
but this would only produce hits for an exact match, and my range contains long strings of text, whereas my search terms are single words. I'd get no hits from this function
I want a function like this:
=--(COUNTIF(range,"*A1*")>0)
but of course, this won't work because this is not a cell reference. Hopefully you can see what I am trying to do.
Does anyone have any ideas for how to write an appropriate function?
TIA
Ben