Search code examples
regexgoogle-sheetsgoogle-sheets-formulagoogle-query-languagemultiple-matches

Google Sheets Query Works with Contains but not with Matches

I can't figure out why my query with Matches does not work while it works fine with contains. I want to eventually pipe additional things into the match but need it to work first.

Contains: =QUERY(Raw!A2:P,"SELECT * WHERE K contains '<m>, 5/2/2020' ",0)

Matches: =QUERY(Raw!A2:P,"SELECT * WHERE K matches '.*<m>, 5/2/2020.*' ",0)

it semi-works when I remove/change the 5 but doesn't return all the matches... the sample data provided won't allow testing but I tested with 2,3,4,6 for example: =QUERY(Raw!A2:P,"SELECT * WHERE K matches '.*<m>, 3.*' ",0)

Sample data for one cell in column K:

Keith <k>, 5/2/2020: @Me: Thank you
_______________________
Keith <k>, 5/2/2020: @S: this is some text
_______________________
Mo <m>, 5/2/2020: @Me: more text is here
_______________________
ME <me>, 5/6/2020: text is here
_______________________
Jonathan <j>, 5/6/2020: @Me: Thank your
_______________________
Jonathan <j>, 5/6/2020: @S: text
Solution

  • Query's matches in Google sheets web app(unlike official mobile apps) doesn't seem to support regex flags like single line mode: (?s). Consider creating a issue with a link to this post in

    Workarounds:

    • Query function seems to run client side unlike other functions. If supported use, (?s) like:

      =QUERY(Raw!A2:P,"SELECT * WHERE K matches '(?s).*<m>, 5/2/2020.*' ",0)

    • If not, simulate single line mode using (.|\n) (any character or new line):

      =QUERY(Raw!A2:P,"SELECT * WHERE K matches '(.|\n)*<m>, 5/2/2020(.|\n)*' ",0)

    • Use contains instead. contains with OR should simulate matches' pipe | joining.

      =QUERY(Raw!A2:P,"SELECT * WHERE K contains "&JOIN( " or K contains ","'<m>, 5/2/2020'" ,"'<k>'")&"",0)