I have the following query:

=QUERY('Data'!A2:F,"SELECT A, B, F WHERE C=''")

In column E I also have strings which correspond to last names. I want to select all rows where the string in E has more than one char. I've tried using the MATCH and LIKE operators but haven't managed to get it to work. One solution I actively avoided was using an extra column with the LEN function and select based on the values there. I would like the logic to be contained in the query as much as possible.

1 Answer 1


So you can use the like operator with query like this:

=QUERY(Data!A2:F,"SELECT A, B, F WHERE C='' and E like '__%'")

basically _ means any one character, and % means 0 or more characters - so by using 2 instances of _ before using % it requires there be at least more than one characters, while also allowing it the flexibility of using a wildcard.

enter image description here

  • That's it! I was trying just with =QUERY(Data!A2:F,"SELECT A, B, F WHERE C='' and E like '__'") and it wasn't working, now I understand why. Thanks!
    – tiredoak
    Commented Dec 11, 2016 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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