GoogleスプレッドシートのQuery関数で複数行セルにmatchsをヒットさせるには

Query関数とは

Googleスプレッドシートのワークシート関数にQueryというのがあって、これがSQL風の記述で他のセルや配列から条件に合った者を抽出できて超便利。検索条件も色々あるが、自分は正規表現による複雑な検索が可能な matches をよく使います。

matches が改行を含んだセルにヒットしない

なぜか存在するはずのクエリ結果が空になる謎現象があり、調べてみるとセルに改行が入っていた。
一行目にmatchesにヒットするはずの部分文字列があり、二行目には何も入ってなかった。
（二行目に文字があってもなくても結果は同じ）
何が起きてるのかはよくわからないが、matchesは複数行のセルを正しく解釈してくれないようだ。
（starts withとかだと大丈夫）

対策

以前似たような記事を書いたが、
それと同じ発想で改行を無くしてしまえばよいのではないか、と考えて出来た解決策がこれ。

=QUERY(ARRAYFORMULA(SUBSTITUTE('シート1'!A:X,CHAR(10),"")),"select Col1... where Col1...")

見ての通り改行コードCHAR(10)をヌル文字で置換しました（除去とも言う）。改行を維持したいなら一旦適当な文字列で置き換えて、再度復元したらいいでしょう。

例えばこう。

=SUBSTITUTE(QUERY(ARRAYFORMULA(SUBSTITUTE('シート1'!A:X,CHAR(10),"<BR>")),"select Col1... where Col1..."),"<BR>",CHAR(10))

それにしてもQuery関数は色々癖があって、合わせるのに苦労しますね・・・