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

  • Thread starter Function_Challenged
  • Start date
F

Function_Challenged

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.
 
T

T. Valko

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!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top