3

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

player0
  • 124,011
  • 12
  • 67
  • 124
Lyber
  • 147
  • 1
  • 4
  • 20

2 Answers2

2

you need to use TEXTJOIN for dynamic list in C column:

=IF(TEXTJOIN( , 1, C3:C)<>"", FILTER(A2:A, REGEXMATCH(LOWER(A2:A), 
 TEXTJOIN("|", 1, LOWER(C3:C)))), "no input")

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thanks! I did try something like **"=FILTER(H2:H,REGEXMATCH(H2:H,TEXTJOIN("|",TRUE,D2:D20)))"** however it wasn't case sensitive, your works perfectly! – Lyber Oct 09 '19 at 03:26
1

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

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • Oh! I didn't know you could use C3 & "|" C4 instead of just C3|C4, that answered my question! But if I have more cells to filter, will I be adding more lines to it such that **=IF(C3<>"", FILTER(A2:A,REGEXMATCH(A2:A,C3&"|"&C4&"|",&C5)),"no input")** by adding more like C5,C6,C7 etc – Lyber Oct 08 '19 at 09:16
  • @Lyber OK, I edited the answer with a more generic approach. Use `JOIN("|", C3:C4)` – Wiktor Stribiżew Oct 08 '19 at 09:21
  • Thank you! With the join formula, everything looks much neater. From there I found TEXTJOIN which is able to ignore blanks too – Lyber Oct 09 '19 at 03:26
  • @Lyber I added that bit to the answer with a link to the docs. Also, as I see it makes a difference, I added a case insensitive variation. – Wiktor Stribiżew Oct 09 '19 at 06:52