How To Check If RANGE OF TEXTSTRINGS Contains ANY of the SEVERAL TEXT KEYWORDS In Excel?

[mtahreemalam]

Hello,
I have a range of TEXT STRINGS in column A, and a list of WORDS in column B. I want to highlight all cells which in column A that contain or match ANY WORD from the text string in column A.

 

[mtahreemalam]

Thankyou for all and Peter. That was exactly the problem i was facing!

 

[DanteAmor]

Since you are trying to match "WORDS" I think the previous suggestions will fail since they will match any substring rather than whole words.

Hi @mtahreemalam, With the characteristic mentioned by Peter, then my formula would look like this:

=SUM(--ISNUMBER(SEARCH(" "&$B$1:$B$400&" "," "&A2&" ")))

 

[Peter_SSs]

Thankyou for all and Peter. That was exactly the problem i was facing!

You're welcome. Glad to help.

BTW, I just noticed that something happened to my CF formula when posting with XL2BB. ?
The formula should have posted as:

=LOOKUP(9.99E+307,SEARCH(" "&$B$2:$B$196&" "," "&A2&" "))

.. or an even simpler CF formula would be

=COUNT(SEARCH(" "&$B$2:$B$196&" "," "&A2&" "))

 

[mtahreemalam]

Hi @mtahreemalam, With the characteristic mentioned by Peter, then my formula would look like this:

=SUM(--ISNUMBER(SEARCH(" "&$B$1:$B$400&" "," "&A2&" ")))

Thankyou so much Dante once again!

 

[DanteAmor]

Thankyou so much Dante once again!

I'm glad to help you. Thanks for the feedback.