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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rllewis - 12 Jul 2007 20:27 GMT

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?

Reply to this Message

Bob Greenblatt - 12 Jul 2007 20:50 GMT

On 7/12/07 3:27 PM, in article
1184268438.928804.313870@n2g2000hse.googlegroups.com, "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?

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

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

JE McGimpsey - 12 Jul 2007 22:54 GMT

One way:

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

Reply to this Message

rllewis - 13 Jul 2007 02:07 GMT

> One way:
>
> = 2 - COUNTIF(P3,"*word*")

JM:

Can you explain this to me?

Reply to this Message

PhilD - 13 Jul 2007 08:29 GMT

> > One way:
>
[quoted text clipped - 3 lines]
>
> 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

--
<><

Reply to this Message

JE McGimpsey - 13 Jul 2007 12:27 GMT

> > > One way:
> >
[quoted text clipped - 10 lines]
> The formula is saying "count the number of times "*word*" appears in
> cell P3, then deduct it from 2.

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.

Reply to this Message

Mac Forum / Applications / Excel / July 2007

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