Using Custom Formula in Conditional Formatting to highlight cells containing string from comma-delimited list in a single cell

0
votes

I'm trying to write a custom formula for conditional formatting in a Google Sheet where, if a cell in Column A contains a word from a comma deliminated list in cell C1, it will highlight.

The closest I have gotten was this:

=SUMPRODUCT(--isnumber(search(split($C$1, ",", true, true), A:A)))=counta(split($C$1, ",", true, true))

Which returned "True" but I cannot quite figure out how to adapt to the conditional formatting syntax.

I have also tried this, but the query syntax does not like the "," in there, and escape characters don't seem to work.

QUERY(Sheet1!A:A, "SELECT A WHERE split($C$1, ",", true, true) ")

Here's the sample spreadsheet. Basically, I want the cells in column A containing "tree" or "car" or "cat" to highlight in green.

2
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygs-conditional-formatting

2 Answers

1
votes

as you say: highlight cells containing string from comma-delimited list in a single cell,

let try:

=COUNTIF($C$1,"*" &A2&"*")*(A2<>"")

enter image description here

0
votes

try:

=INDEX(REGEXMATCH(A1, TEXTJOIN("|", 1, SUBSTITUTE(C$1:C, ",", "|"))))

0

=INDEX(REGEXMATCH(C1, TEXTJOIN("|", 1, SUBSTITUTE(A$1:A, ",", "|"))))

0