Conditionally format specific values within comma-separated values

Question

Is there a way to apply conditional formatting to specific values within a cell of comma-separated values?

  Background

1. In a Google form, I have a Checkboxes-type question with 50 options that increment by 1, from CACHE 1 to CACHE 50 .
   _{Checkbox Question ( Form )}
   
    

2. In Google Sheets, each form response combines the selected options in a single column as comma-separated values.
   _{Form Responses ( Spreadsheet )}
   
   

   	A	B
   1	Timestamp	Check all that apply:
   2	9/6/2024 15:59:41	CACHE 1, CACHE 3, CACHE 17, CACHE 29
   3	9/6/2024 15:59:45	CACHE 3, CACHE 29
   4	9/6/2024 15:59:49	CACHE 1, CACHE 17, CACHE 49, CACHE 50
   5	9/6/2024 16:01:10	CACHE 29, CACHE 49

3. I want to apply conditional formatting to individual options within the comma-separated values but can't figure out how.
   _{Desired Result ( Spreadsheet )}
   

Answer 1

Not without using a script-based solution, however, there may not be the need.

• Best practices encourage us to avoid doing anything to raw form response data but instead use other sheets to manipulate the data as needed.

• Conditional formatting rules cannot be applied to parts of a cell, however, the results can be separated into multiple cells and formatting can then be applied.

Variations

A couple of approaches that can be adapted to your needs.

1.  Split to Columns with Formatting

=LET(rng,'Form Responses 1'!A2:B,
   BYROW(FILTER(rng, LEN(INDEX(rng,,1))), LAMBDA(row, 
     TOROW(MAP(SEQUENCE(COLUMNS(row)), LAMBDA(col, 
       LET(val,INDEX(row,,col),
         IF(col=2, SPLIT(val,", ",), val)))),1))))

BYROW  COLUMNS  FILTER  IF  INDEX  LAMBDA  LEN  LET  MAP  SEQUENCE  SPLIT  TOROW


 

2.  Character in Options' Column

=INDEX(LET(
  rng,'Form Responses 1'!A2:B,
  cols,SEQUENCE(COLUMNS(rng)), optCol,2,
  opts,50, seq,SEQUENCE(1,opts),
  BYROW(FILTER(rng, LEN(INDEX(rng,,1))), LAMBDA(row, 
    { CHOOSECOLS(row, FILTER(cols, cols<optCol)),
      IF(REGEXMATCH(INDEX(row,,optCol), "CACHE "& seq &"\b"), "✔",),
      IFERROR(CHOOSECOLS(row, FILTER(cols, cols>optCol))) }))))

BYROW  CHOOSECOLS  COLUMNS  FILTER  IF  IFERROR  INDEX  LAMBDA  LEN  LET  REGEXMATCH  SEQUENCE