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.

  • Have you tried the query or the updated answer following your comment? Did any work for you? – marikamitsos Oct 25 '19 at 19:03
  • Hi @marikamitsos, I asked this question on stackoverflow separately and received an answer there using a Regex, which solved my follow-up question. Thanks for your help in answering this question as well! – Lou Oct 25 '19 at 19:42
  • It is interesting that you chose the FILTER solution (which needs an additional helper column) over your original QUERY one. Also, regarding the answer in stackoverflow, it is on a diferrent site as well as just a part of the whole formula. Not the answer to it. In any case, glad it all worked well. – marikamitsos Oct 25 '19 at 23:39
  • Hi @marikamitsos, I only needed the part rather than the whole, as the REGEXMATCH solution worked fine for my purposes even with a helper column. Thanks for your assistance, but I'm happy with the answers. – Lou Oct 27 '19 at 16:57

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


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

|improve this answer|||||
  • Thanks so much for this! I don't suppose you know how I could modify it if I wanted to make words which only contain those characters pop up? And none others? – Lou Oct 18 '19 at 19:39

You can use your original QUERY function.
You just have to change like to matches along with the TEXTJOIN function.
It is matches that allows you to use a regular expression (regex) in the QUERY function.

=QUERY(B14:B,"select B where B matches '.*["& TEXTJOIN(,true,A14:A16) &"].*'")

following OP's comment:
...make words which only contain those characters pop up and none others

You only change the regular expression included in the query:

=QUERY(B14:B,"select B where B matches '^(?:(["&TEXTJOIN(,true,A14:A16)&"]))+$'")
|improve this answer|||||
  • Following your comment. Please check the updated answer. – marikamitsos Oct 18 '19 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.