Google-sheets – ‘contains’ and ‘starts with’ cannot match text with a character after a hyphen

formulasgoogle sheetsgoogle-sheets-query

Google Sheets queries cannot match text using contains or starts with when there the text to match contains a hyphen, and the hyphen is not the last character in the text to match. Am I doing something wrong? If not, is this a bug? Is there a workaround?

Example sheet.

"select count(A) where A starts with '2021-'" will return the count of matching results.

"select count(A) where A starts with '2021-0'", on the other hand, always claims there are no matching results even if there actually are.

Best Answer

The query() function happily matches text that contains hyphens.

When you use the contains operator, data in columns of type date gets automatically converted to text in the yyyy-m-d format, lacking leading zeros, regardless of their original format.

You can get what you ask for by converting the dates to text before feeding them to query() like this:

=arrayformula( query( trim(A2:A8), "select count(Col1) where Col1 starts with '2021-0' ", 0 ) )

But this is not a very good way to find dates before October, because it depends on the date format in the spreadsheet remaining yyyy-mm-dd. A better solution would not rely on the format, like this:

=query(A2:A8, "select count(A) where year(A) = 2021 and month(A)+1 < 10", 0)

Months are zero-indexed, hence the +1.

Related Topic