Need Excel count of 1 word if found in multi-word cells of column
 

I need a function that tests whether a cell containing multiple words
includes certain text. For example, test for the occurrence of the string
"alarm" and give me a count. One spreadsheet cell contains all of the
following text:

The event alarmed 3 times with 1 alarm being critical.

The text string "alarm" actually appears twice in that statement, one of
which was a word. What two sets of syntax will allow me to capture both
conditions...a string match or a word match.

Need Excel count of 1 word if found in multi-word cells of column
 

For a string match:

=COUNTIF(A1,"*alarm*")0

For a word match:

=ISNUMBER(SEARCH(" alarm "," "&A1&" "))

Note that the word match is not 100% reliable! For a word match we assume
there will be spaces on either side of the word.We can pad the phrase with
spaces on both ends to catch mathes that occur at the very beginning and at
the very end of the phrase but this still trips when punctuation marks are
present. For example:

Alarm 3 lasted 5 days
The 3rd alarm lasted 5 days
There was no alarm

The formula will work in all of those examples but will fail in these:

Alarm3 lasted 5 days
The 3rd "alarm" lasted 5 days
There was no alarm!!!

--
Biff
Microsoft Excel MVP


"Function_Challenged" wrote
in message ...
I need a function that tests whether a cell containing multiple words
includes certain text. For example, test for the occurrence of the string
"alarm" and give me a count. One spreadsheet cell contains all of the
following text:

The event alarmed 3 times with 1 alarm being critical.

The text string "alarm" actually appears twice in that statement, one of
which was a word. What two sets of syntax will allow me to capture both
conditions...a string match or a word match.