Sheets query matches with new line

0
votes

How do you get a sheets query to match within cells that contain new line characters?

I tried matches '.*Something.*and (?s).*Something.* but I can't get it to work.

I would rather not change the data that is fed into the query.

Here is a sample sheet: https://docs.google.com/spreadsheets/d/1sYqhuW-5ldfz69LZjSI_L6FwdfGs6pXeMakJKrtMsCw/edit?usp=sharing

2
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language
I'm pretty sure there's got to be a way to make this happen. But I haven't been able to make it possible through sheet functions. Would you be down for a custom functions in Apps Script? Have you used Apps Script before? – Raserhin
I have used app scripts before, I am looking for a solution to be able to use the built in "Query" function though. – novice
So what is the reason why @player0 answer does not work for you? Does creating an additional helper column be feasible for you? – Raserhin
@player0's answer wasn't using "match" which enables you to use other regex patterns. I wrote in the question that a helper column wouldn't either be ultimate solution. – novice

2 Answers

0
votes

This is what I have come up with: 

=QUERY(A2:A7, "where lower(A) matches '(.*\n)*.*(pear).*(\n.*)*'")

The lower is completely optional I just used to help me with the testing. Just remember to adapt your regEx accordingly.

Hope this works for you because I took an embarrassing amount of time to do this.

Example inside Sheet

1
votes

you can do:

=QUERY(A2:A7, "where lower(A) contains 'pear'")

0