ExcelBanter - Contains the Word(s)

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Contains the Word(s) (https://www.excelbanter.com/excel-worksheet-functions/123975-contains-word-s.html)

Contains the Word(s)

Is there a conditional formatting formula that can format an entire row a
certain way if Column A or Column B contain all or one of the words in a list.
Basically if either Column A or Column B contain "dog" and/or "cat" and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.

Contains the Word(s)

Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid" wrote
in message ...
Is there a conditional formatting formula that can format an entire row a
certain way if Column A or Column B contain all or one of the words in a
list.
Basically if either Column A or Column B contain "dog" and/or "cat" and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.

Contains the Word(s)

Using the same setup as Bob, I'd use the formula

=SUM(ISNUMBER(SEARCH($M1:$M$10,$A2&" "&$B2))*($M$1:$M$10<""))

"Bob Phillips" wrote:

Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid" wrote
in message ...
Is there a conditional formatting formula that can format an entire row a
certain way if Column A or Column B contain all or one of the words in a
list.
Basically if either Column A or Column B contain "dog" and/or "cat" and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.

Contains the Word(s)

The formula ended up highlighting everything, whether the words were located
in the column or not.
Are you sure this would be the formula to format Row X if Row X has Dog,
Cat, etc in column A or B?
What could I have done wrong???? (So close, yet so far!)

"Bob Phillips" wrote:

Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid" wrote
in message ...
Is there a conditional formatting formula that can format an entire row a
certain way if Column A or Column B contain all or one of the words in a
list.
Basically if either Column A or Column B contain "dog" and/or "cat" and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.

Contains the Word(s)

daddylonglegs wrote...
Using the same setup as Bob, I'd use the formula

=SUM(ISNUMBER(SEARCH($M1:$M$10,$A2&" "&$B2))*($M$1:$M$10<""))

"Bob Phillips" wrote:
Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),
AND($B2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))
....

Also using Bob's setup,

=SUM(COUNTIF($A2:$B2,"*"&$M$1:$M$10&"*"))

to match words in M1:M10 as substrings in A2:B2 or

=SUM(COUNTIF($A2:$B2,$M$1:$M$10))

to match words in M1:M10 as entire contents of cells in A2:B2.

Contains the Word(s)

Yes, I am absolutely sure. As given by me, the first row selected should be
row 2, if yours is different, change the A2 and B2.

But, Harlan gave a much better formula than mine (and I can vouch for that
one as well).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid" wrote
in message ...
The formula ended up highlighting everything, whether the words were
located
in the column or not.
Are you sure this would be the formula to format Row X if Row X has Dog,
Cat, etc in column A or B?
What could I have done wrong???? (So close, yet so far!)

"Bob Phillips" wrote:

Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid"
wrote
in message ...
Is there a conditional formatting formula that can format an entire row
a
certain way if Column A or Column B contain all or one of the words in
a
list.
Basically if either Column A or Column B contain "dog" and/or "cat"
and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.

Contains the Word(s)

SUCCESS! Yesterday was too long of a day. A $ was missing from one of the
formulas.
I ended up using
=SUM(ISNUMBER(SEARCH($M$1:$M$10,$A2&" "&$B2))*($M$1:$M$10<""))
....and it works BEAUTIFULLY!
THANKS SO MUCH FOR YOUR HELP, HARLAN AND BOB!

"Bob Phillips" wrote:

Yes, I am absolutely sure. As given by me, the first row selected should be
row 2, if yours is different, change the A2 and B2.

But, Harlan gave a much better formula than mine (and I can vouch for that
one as well).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid" wrote
in message ...
The formula ended up highlighting everything, whether the words were
located
in the column or not.
Are you sure this would be the formula to format Row X if Row X has Dog,
Cat, etc in column A or B?
What could I have done wrong???? (So close, yet so far!)

"Bob Phillips" wrote:

Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid"
wrote
in message ...
Is there a conditional formatting formula that can format an entire row
a
certain way if Column A or Column B contain all or one of the words in
a
list.
Basically if either Column A or Column B contain "dog" and/or "cat"
and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.

Contains the Word(s)

Harlan's was better than that one

=SUM(COUNTIF($A2:$B2,"*"&$M$1:$M$10&"*"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid" wrote
in message ...
SUCCESS! Yesterday was too long of a day. A $ was missing from one of
the
formulas.
I ended up using
=SUM(ISNUMBER(SEARCH($M$1:$M$10,$A2&" "&$B2))*($M$1:$M$10<""))
...and it works BEAUTIFULLY!
THANKS SO MUCH FOR YOUR HELP, HARLAN AND BOB!

"Bob Phillips" wrote:

Yes, I am absolutely sure. As given by me, the first row selected should
be
row 2, if yours is different, change the A2 and B2.

But, Harlan gave a much better formula than mine (and I can vouch for
that
one as well).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid"
wrote
in message ...
The formula ended up highlighting everything, whether the words were
located
in the column or not.
Are you sure this would be the formula to format Row X if Row X has
Dog,
Cat, etc in column A or B?
What could I have done wrong???? (So close, yet so far!)

"Bob Phillips" wrote:

Put the words in a list, say M1:M10, and use a formula of

=OR(AND($A2<"",SUMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$A2))))),AND($B2<"",S UMPRODUCT(--(ISNUMBER(SEARCH($M$1:$M$10,$B2))))))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Keep It Simple Stupid"
wrote
in message ...
Is there a conditional formatting formula that can format an entire
row
a
certain way if Column A or Column B contain all or one of the words
in
a
list.
Basically if either Column A or Column B contain "dog" and/or "cat"
and/or
"mouse" and/or ....
then I want the whole row to be flagged/shaded/etc.