Search code examples
regexgoogle-sheetsfiltergoogle-sheets-formulatextjoin

Filter with REGEXMATCH in Google sheet to filter out containing text in cells

Right now I have these data and I'm trying to filter out the data containing in cell C3, C4, etc.

I have no problem filtering the regexmatch data for 1 cell as shown below

enter image description here

but I'm unable to do regexmatch for more than 2 cells like so for example, it seems like I'm unable to make the pipework between cells as I'll get parse error, I tried adding in "C3|C4" too.

enter image description here

and

enter image description here

The wanted output that I wanted is as below but I could only hardcode the containing text in which isn't what I'm looking for. I'm hoping that I could have some tips to regexmatch the text in more than 1 cell such that it could regexmatch the text in cell C3(Apple) and C4(Pear) and show the wanted output.

enter image description here

Solution

  • You may use

    =IF(C3<>"", FILTER(A2:A,REGEXMATCH(A2:A, TEXTJOIN("|", TRUE, C3:C4) )), "no input")

    Or, you may go a step further and match Apple or Pear as whole words using \b word boundaries and a grouping construct around the alternatives:

    =IF(C3<>"", FILTER(A2:A,REGEXMATCH(A2:A, "\b(?:" & TEXTJOIN("|", TRUE, C3:C4) & ")\b")), "no input")

    And if you need to make the search case insensitive, just append (?i) at the start:

    =IF(C3<>"", FILTER(A2:A,REGEXMATCH(A2:A, "(?i)\b(?:" & TEXTJOIN("|", TRUE, C3:C4) & ")\b")), "no input")

    See what the TEXTJOIN documentation says:

    Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts.

    So, when you pass TRUE as the second argument, you do not have to worry if the range contains empty cells, and the regex won't be ruined by extraneous |||.

    Test:

    enter image description here