I have covered almost all the essential tips and tricks to help my readers to learn the Google Sheets Query function. But one of the missing tutorials is examples of using the Ends With substring suffix match in Query.

I have included the string-matching tips associated with the suffix match in this tutorial.

The Ends With substring match helps Query users to filter a column based on suffix match. It must be used in the Where clause in Query.

Further, you can use the Query Logical operator Not to negate the Ends With suffix match. All that you can learn in this Query tutorial.

Ends With Suffix Match in Query

You can use the Query Ends With substring suffix match in the following similar scenarios in real-life in Google Sheets.

Suppose you want to filter a column where texts in each row end with a specific word, phrase, or alphabet.

How to code a Query formula using Ends With suffix match to handle this?

For example, I have a few names and their email addresses as below in column A.

I can filter all the email addresses from the column using the below Query.

=query(A1:A,"Select A where A ends with 'com'")

Then how to filter only the names? Here you can use the Not logical operator in Query.

Not Logical Operator with Ends With Suffix Match in Query

The Not Logical operator must come before the column identifier.

Please do not use the logical operator directly with the Ends With substring suffix match like Not Ends With.

The Query formula that negates the Ends With substring match must be as follows.

=query(A1:A,"Select A where not A ends with 'com'")

I hope the above given two Query formulas are enough for one to learn the Ends With and Not Ends With suffix match in Query.

But of course, you may have some more doubts.

For example;

1. Can I use Ends With and Not Ends With Suffix Match with a date column?
2. Can I use the Ends With and Not Ends With Suffix Match with a number Column?

The Ends with suffix match is not fit to filter a date column in Google Sheets.

If you are so particular, first, format the dates to text.

You can use the To_text function with Query to use a substring match.

But do remember to change the column identifiers from alphabets to numbers like A to Col1.

I have addressed these problems below.

Filter a Date Column Using Suffix Match in Query

Query in Dates Ends With:

=ArrayFormula(query(to_text(A1:A),"Select Col1 where Col1 ends with 8"))

Query in Dates Not Ends With:

=ArrayFormula(query(to_text(A1:A),"Select Col1 where not Col1 ends with 8"))

The ArrayFormula use is justified since the non-array to_text function is part of the formula.

Similar to a text string, you can use the Ends With to suffix match a number column.

Filter a Number Column Using Suffix Match in Query

Unlike the date column, you can use the suffix match in Query within a number column without formatting that column to text.

Query to Match Number Ends With:

=query(A1:A,"Select A where A ends with .25")

Query to Match Number Not Ends With:

=query(A1:A,"Select A where not A ends with 0")

That’s all. Enjoy!

Related Resources (String Operators):

1. Matches Regular Expression Match in Google Sheets Query.
2. CONTAINS Substring Match in Google Sheets Query for Partial Match.
3. How to Use LIKE String Operator in Google Sheets Query.
4. Learn Query Starts With/Not Starts With Prefix Match.