Function to seacrh for a word across multiple columns

[Vibeke]

Hi, I have four columns of data (C-F) I need to search for a particular word.
The data is addresses, i.e. it combines text and numbers and the word I'm
searching for can appear anywhere within it (or not at all). The function I
require will return 1 if it does and 0 if it does not. Have tried various
iterations of if\search\match and so on to no avail - perhaps because I'm
searching a range of columns rather than a specific cell? I'd appreciate your
suggestions. Regards,

 

[T. Valko]

Maybe something like this...

=COUNT(MATCH("*word*",C1:F1,0))

 

[Steve Dunn]

or,

=--(countif(c1:f1,"*word*")>0)

but there is a possible problem. If the word you are searching for appears
within another word. e.g. "forward" contains "for", "or", and "ward", so if
your cells contained "forward" and you were looking for one of those words,
they would be found even if they weren't actually there. A slight variation
would be:

=--(countif(c1:f1,"* word *")>0)

but you would then have a situation where words at the beginning or end of a
string, or words with punctuation beside them, would not be found. So the
answer to this one could be quite a bit more complex than you would think,
is that why you have ignored the answers given to your earlier post?