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.
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 inC1
:It filter the range
B2:B100
based on which result returns the regexExplanation :
The following formula returns
False
orTrue
based on the content in the cellsB2:B100
. The operator|
is basically aOR
in this regex.Breakdown :
Live demo