Search one cell for specified words

[Shoey68]

Hi there,
I'm trying to count if 1, 2 or all 3 of a selection of words appear in certain cells.
For instance. Three cells might contain:
A1 "The cat sat on the mat"
A2 "The dog chased the ball"
A3 "Then the dog sat on the mat"

I need a formula to identify whether or not each cell contains the words "cat", "sat" or "mat", but not count the occurences.
The result should be like:
B1 "1" (all three words appear, but I need Excel to count only 1 occurence)
B2 "0" (none of the words appear)
B3 "1" (2 words appear, but agian I need Excel to count only once"

Apologies if I haven't explained this very well and thanks in advance for your help!!

 

[Ron Coderre]

With
A1: containing a text string
and
D1:D3 containing this list
cat
sat
hat

This regular formula returns 1 if A1 contains any of the listed words

B1: =--OR(ISNUMBER(SEARCH(" "&D$1:D$3&" "," "&A1&" ")))

Is that something you can work with?

 

[Shoey68]

Thanks for the quick reply. Unfortunately, I can only get this method to work for the top row of a worksheet - I need to count a few hundred cells, in the same column. (I can use a 'spare', hidden sheet to store the 3 or 4 words I need to summarise...

Thanks again

 

[Ron Coderre]

Please post the formula you're using...perhaps we'll spot an issue with it.

 

[Shoey68]

Below is my example (Just in case you're wondering, I'm not really counting cats & mats - It's just easier to explain that way!)

A1: the cat sat on the mat
A2: the dog chased the ball
A3: then the dog sat on the mat
A4: the cat chased the ball

D1: cat
D2: sat
D3: mat

B1: =--OR(ISNUMBER(SEARCH(" "&D$1:D$3&" "," "&A1&" ")))
B2: =--OR(ISNUMBER(SEARCH(" "&D$1:D$3&" "," "&A2&" ")))
B3: =--OR(ISNUMBER(SEARCH(" "&D$1:D$3&" "," "&A3&" ")))
B4: =--OR(ISNUMBER(SEARCH(" "&D$1:D$3&" "," "&A4&" ")))

Results:
B1: 1
B2: 0
B3: 1
B4: 0

Expected Results
B1: 1
B2: 0
B3: 1
B4: 1

Thanks again for you time

 

[jasonb75]

The formula needs to be array entered to work correctly.

 

[Shoey68]

Hi Jason,

How do I do that???

 

[T. Valko]

Hi there,
I'm trying to count if 1, 2 or all 3 of a selection of words appear in certain cells.
For instance. Three cells might contain:
A1 "The cat sat on the mat"
A2 "The dog chased the ball"
A3 "Then the dog sat on the mat"

I need a formula to identify whether or not each cell contains the words "cat", "sat" or "mat", but not count the occurences.
The result should be like:
B1 "1" (all three words appear, but I need Excel to count only 1 occurence)
B2 "0" (none of the words appear)
B3 "1" (2 words appear, but agian I need Excel to count only once"

Apologies if I haven't explained this very well and thanks in advance for your help!!

Here's another possibility:

=--(SUM(COUNTIF(A1,"*"&{"cat","sat","mat"}&"*"))>0)

Copy down as needed.

 

[Shoey68]

That's superb!!

Worked a treat

Thanks for all your help!!!

 

[T. Valko]

That's superb!!

Worked a treat

Thanks for all your help!!!

You're welcome. Thanks for the feedback!