I use a spreadsheet where I want a list of items which contains multiple criteria. (I need all cells in a range which contains the letter 'a - g'

The formula I use now is

=query(Planning!G5:G37;  "select * where G contains 'a' or G contains 'b' or G contains 'c'   or ............  " )

What I want is the query get the criteria out of a list or an array, but I couldn't figure out how to do this.

  • you can use regex if you do it like this:

    =QUERY(Planning!G5:G37, "where G matches '[a-g]'", 0)

    =QUERY(Planning!G5:G37, "where G matches 'a|b|c|d|e|f|g'", 0)


  • to mimic contains do:

    =QUERY(G5:G37, "where G matches '[a-g]*|.*[a-g]'", 0)


  • Thank you, that work good for me. – Richard de Ree May 30 '19 at 5:50

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.