It seems there may be a bug in Google Sheets' Query formula where results with a new line character (char 10) are excluded when the column is filtered. Is there any way around this or am I doing something wrong?

source data in columns A:C, query formula in E1 doesn't return rows 5 or 6 even though the filter criteria says they should.

  • 1
    The Query in E1 should also be returning the values in A5:C6 as they meet the criteria: where B like '%2%' – Cluelessness Feb 14 at 5:38
  • this is not a fault of CHAR(10) nor bug
  • by examining the whole range with =ARRAYFORMULA(ISNUMBER(A2:C7)) you can see that CHAR(10) causes cells with numbers to act like TEXT which makes sense because there is not a number like 19 68 it's either 19, 68 or 1968

  • therefore, you will need to use such query that works with text not with numbers:

    =QUERY(A1:C7, "select A,B,C where B contains '2'")

    enter image description here

  • 1
    very interesting... the project I was working on had the issue on cells with text but using contains seems to have fixed it! Thanks! – Cluelessness Feb 14 at 5:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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