Search code examples
google-sheetsgoogle-query-language

Apostrophes in the same string in QUERY Google Sheets function


=TRANSPOSE(QUERY(sorting!A57:A,"select * where A contains '"&$A57&"'"))

This is what I'm currently using to make a word tree based on my A column. I want to find all instances of A57 in the A column and fill the rest of the row with those. I have 2 issues that I can't seem to squash:

  1. If I have an apostrophe it fails (circled in green in the screenshot)
  2. I want whole word matches and not parts of words (circled in red) e.g. "is" should return "is he" "is it" "is it the" and NOT "fish" "wish" etc.

Google Sheet Screenshot


Solution

  • SOLVED!

    =TRANSPOSE(QUERY(sorting!A1:A,"select * where A matches "".\b"&$A1&"\b."""))

    both problems 1 and 2 solved. thanks.