How to use a formula that tests whether a cell contains a word

[rllewis]

I need a formula that detects a particular word in a cell, even if
there are more words in that cell. Example would be =IF((CONTAINS
"word",P3),1,2). IE: if cell P3 contains the word "word" then 1, else
2. Anybody?

 

[Bob Greenblatt]

I need a formula that detects a particular word in a cell, even if
there are more words in that cell. Example would be =IF((CONTAINS
"word",P3),1,2). IE: if cell P3 contains the word "word" then 1, else
2. Anybody?

=if(not(iserror(search("word",p3))),1,2)

 

[JE McGimpsey]

I need a formula that detects a particular word in a cell, even if
there are more words in that cell. Example would be =IF((CONTAINS
"word",P3),1,2). IE: if cell P3 contains the word "word" then 1, else
2. Anybody?

One way:

= 2 - COUNTIF(P3,"*word*")

 

[rllewis]

One way:

= 2 - COUNTIF(P3,"*word*")

JM:

Can you explain this to me?

 

[PhilD]

JM:

Can you explain this to me?

The asterisks mean "anything", so as long as the cell contains the
letters "word", it will return a result. For example, "words" and
"sword" and "swords" will also return a result.

The formula is saying "count the number of times "*word*" appears in
cell P3, then deduct it from 2.

So, if the cell does not contain "word", that is a zero count, so the
formula works out 2-0, which gives you a 2 if "word" is not present.

If the cell does contain "word", that is a one count, so the formula
works out 2-1, which gives you a 1 if "word" is present.

PhilD

 

[JE McGimpsey]

JM:

Can you explain this to me?

The asterisks mean "anything", so as long as the cell contains the
letters "word", it will return a result. For example, "words" and
"sword" and "swords" will also return a result.

The formula is saying "count the number of times "*word*" appears in
cell P3, then deduct it from 2.[/QUOTE]

Close, but not quite. COUNTIF() returns a count of the number of
*cells* in the range that meet the criterion (no matter how many times
within each cell), so COUNTIF(P3,"*word*") returns the number of cells
in the range P3 contain the text "word". Even if P3 contains
"wordwordword", COUNTIF will only return 1.

So, if the cell does not contain "word", that is a zero count, so the
formula works out 2-0, which gives you a 2 if "word" is not present.

If the cell does contain "word", that is a one count, so the formula
works out 2-1, which gives you a 1 if "word" is present.

Correct.