google-sheetsfiltersplitformulatextjoin

Google Sheets Formula Filter Combined Values - Separated by Comma's

I am currently working on creating a formula in Google Sheets that can filter both individual values and multiple values combined with commas.

I am facing challenges in understanding how to achieve this as my attempts so far have only produced results for individual values.

Your help will be much appreciated in this regards.

=FILTER(B:B,
  (D:D = J2) *
  REGEXMATCH(","&C:C&",", ",(" & SUBSTITUTE(I2, ",", "|,|") & "),")
)

enter image description here

Solution

  • try:

    =FILTER(B:B, D:D=G2, REGEXMATCH(C:C, JOIN("|", TRIM(SPLIT(F2, ",")))))

    or for case-insensitive:

    =FILTER(B:B, D:D=G2, REGEXMATCH(C:C, "(?i)("&JOIN("|", TRIM(SPLIT(F2, ",")))&")"))