Ends With Suffix Match in Google Sheets Query is one of the string comparison operators available in the QUERY function. It allows you to evaluate whether a text value ends with a specific substring and return only the matching rows.

You can also negate this condition using the NOT logical operator to create a Not Ends With suffix match.

These operators are explained here in detail as part of the String Matching in Google Sheets QUERY hub.

ENDS WITH Suffix Match in QUERY

You can use the Ends With suffix match in several real-life scenarios in Google Sheets.

Example use case

Suppose you have a column that contains names and email addresses, and you want to filter only the email addresses.

In most cases, email addresses end with a domain such as com, org, or net.

The following Query formula uses the suffix match "com" to return all values that end with it:

=QUERY(A1:A,"select A where A ends with 'com'")

You can be more specific by matching ".com" instead of just "com".

Alternative operators

ENDS WITH is not the only operator you can use to filter text based on suffixes. Other complex string comparison operators such as LIKE and MATCHES can also be used for this purpose.

These operators support pattern matching. However:

• Use ENDS WITH when you want a simple and direct suffix check
• Use MATCHES when you want to match multiple suffixes (for example, com or org) without writing multiple ENDS WITH conditions

NOT ENDS WITH Suffix Match in QUERY

You can negate the Ends With condition using the NOT logical operator to return rows that do not end with a specific substring.

Important: The NOT operator must appear before the column identifier.

The correct syntax is:

=QUERY(A1:A,"select A where not A ends with 'com'")

This formula returns all values that do not end with "com".

Suffix Match and Case Sensitivity

By default, Ends With suffix match in Google Sheets Query is case-sensitive.

This means:

• "com" will match "example.com"
• "COM" or "Com" will not match unless explicitly handled

Making the match case-insensitive

To perform a case-insensitive suffix match, wrap the column reference with LOWER() or UPPER().

ENDS WITH (case-insensitive)

=QUERY(A1:A,"select A where LOWER(A) ends with 'com'")

NOT ENDS WITH (case-insensitive)

=QUERY(A1:A,"select A where not LOWER(A) ends with 'com'")

Referring to the Suffix Text from a Cell

Sometimes, you may not want to hard-code the suffix text inside the Query formula.

For example, if the suffix (com) is entered in cell B1, you can reference it dynamically:

=QUERY(A1:A,"select A where A ends with '"&B1&"'")

This approach makes your formula more flexible and easier to maintain.

To learn more about using cell references in string matching conditions, see this tutorial:
How to Use Cell Reference in Google Sheets Query

Conclusion

The Ends With Suffix Match in Google Sheets Query is a simple yet powerful way to filter text values based on their ending characters.

• Use ENDS WITH for direct suffix checks
• Use NOT ENDS WITH to exclude specific suffixes
• Apply LOWER() or UPPER() for case-insensitive matching
• Refer to a cell when you want a dynamic suffix condition

These techniques help you write cleaner, more efficient QUERY formulas for real-world data analysis in Google Sheets.