Search code examples
excelconditional-statementsformatlookup

formatting formula Highlight all cells in a column of text strings sentences, that contain any of the phrases or keywords from another column in excel

I have a list of sentences in column A. and a list of keywords and phrases in column B. I need a conditional formatting formula or some other way of highlighting any of the sentences in column a that contain any of the exact phrases or keywords that are in column b.

Column A For example..

There is a sentence here

Example sentence flower stunning

boarding pass example text

sentence containing shabby chic

example large rock sentence

example text hardcore

examples sentence

some text here

Column B For example....

Pink

Black & White Flower

Stunning

Titanium Gold

Boarding Pass

Shabby Chic

Rock With You

Hardcore

Swirly

Fantastic Four

Adolescent

Keyring

Large Rock

My column A has over 100k sentences and column b has over 3000 phrases or keywords. Otherwise I would have gone to conditional formatting, text that contains, and highlight for each word individually. I've spent hours googling with no luck. Below is the closest article I found but not quite what i need.

Conditional formatting for cells that contain words listed in another column

Even if there is a different way that is not highlighting them I'm open to that. I just need to identify every cell in column A that contains one of the words or phrases from column b

Solution

  • Depending on what you mean with an exact match, but the following might work when you want to see if any of the values in the 2nd column exist as a substring in the 1st one:

    enter image description here

    =SUM(COUNTIF($A1,"*"&$B$1:$B$13&"*"))

    A much more stricter version could be to concatenate spaces on either side of the 1st and 2nd column. This will weigh more heavy on the calculation side of things though:

    =SUM(--ISNUMBER(SEARCH(" "&$B$1:$B$13&" "," "&$A1&" ")))