Google-sheets – How to filter a list based on whether it contains any of a list of substrings

google sheetsgoogle-sheets-query

I have a list of Japanese words. I want to produce a formula to filter that list, based on a filter list of substrings (kanji characters) which I'm looking for.

So for example, say my filter list contains three characters, 水 and 木 and 日. I want to find every word in my list which contains 水 or 木 or 日. That's an inclusive or – any words which contain more than one of the characters on the filter list (such as 木曜日 or 水曜日) should show up, too.

enter image description here

So far, my formula works with just one character to filter for:

=QUERY(Sheet1!$A$1:$A$44492, "SELECT A WHERE A LIKE '%"&A2&"%'")

However, I've tried expanding it to a range, and it doesn't work:

=QUERY(Sheet1!$A$1:$A$44492, "SELECT A WHERE A LIKE '%"&A2:A4&"%'")

It only filters words containing the first character, 水.

Is there a way to filter the whole list based on all the items in my list? And to be clear, I want it to be dynamic, so that I can put in as many filter characters as I like to the list, rather than basing it on an exact number of filter characters.

Best Answer

You can use the filter function with REGEXMATCH as a parameter, try this in C1 :

=FILTER(B2:B100,arrayformula(REGEXMATCH(B2:B100,TEXTJOIN("|",true,A2:A4))))

It filter the range B2:B100 based on which result returns the regex


Explanation :
The following formula returns False or True based on the content in the cells B2:B100. The operator | is basically a OR in this regex.

=arrayformula(Regexmatch ( range , "水|木|日" ))

Breakdown : enter image description here

Live demo