CODEWITHSUNDEEP
google-sheets
Rattikarl
Rattikarl

Reputation: 187

Conditional formatting for cells that contain words listed in another column

I have a Column with rows of words where I want to highlight words that are used in other Columns. The other columns however contain sentences. Anyone knows how this is possible? In this example Column A contains the words and the words should be highlighted if they are used in Sentences in all rows of Column B or C.

Column A:

  1. Sausage
  2. Wiener
  3. Brat

Column B:

  1. I like Sausage
  2. I don't like Steaks

Column C:

  1. I like Brat

Upvotes: 2

Views: 831

Answers (1)

user6655984
user6655984

Reputation:

Using regexmatch: create a conditional formatting rule with custom formula

=regexmatch(B1, join("|", filter($A:$A, len($A:$A))))

And apply it to the range B:C (or to another range; the cell given as B1 above must be the upper left corner of that range).

Here filter($A:$A, len($A:$A)) takes the content of nonempty cells, and join creates a regular expression out of them, like Sausage|Wiener|Brat. Cells are highlighted if they match that regex. Some variations:

Include word boundaries, so that only whole words are matched:

=regexmatch(B1, "\b(" & join("|", filter($A:$A, len($A:$A))) & ")\b")

Case-insensitive matching with word boundaries: 

=regexmatch(B1, "(?i)\b(" & join("|", filter($A:$A, len($A:$A))) & ")\b")

Upvotes: 3

Related Questions