Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
count if the cell contains a word found in another cell Go Bucks!!![_2_] Excel Worksheet Functions 2 July 28th 09 02:41 PM
return cells below stop when word found bwilk77 Excel Discussion (Misc queries) 1 April 2nd 09 05:50 PM
Converting a Word doc to a multi-column Excel spreadsheet DD Pgh Excel Worksheet Functions 0 January 12th 09 07:42 PM
How can I graph the #of times a certain word is found in Excel? STP Charts and Charting in Excel 1 May 28th 08 09:32 AM
Merge Excel multi columns into Word [email protected] Excel Worksheet Functions 0 August 16th 06 10:27 AM


All times are GMT +1. The time now is 01:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"