ENDSWITH function

ENDSWITH (Text, Suffix) ENDSWITH (Text; Suffix)

Text

Text Text

The text string to check.

Suffix

Text Text

The text string that the first text string may, or may not, end with.

Returns

Logical Logical

Whether a text string ends with another text string.

Returns whether a text string ends with another text string. ENDSWITH ("support@example.com", "@example.com") ENDSWITH ("support@example.com"; "@example.com") returns TRUE, as "support@example.com" ends with "@example.com".

The comparison is case-sensitive. To perform a case-insensitive comparison, first apply LOWER or UPPER to both parameters:

ENDSWITH ( LOWER ("TESTING"), LOWER ("ing"))

ENDSWITH ( LOWER ("TESTING"); LOWER ("ing"))

This formula returns whether "TESTING" ends with "ing". As both parameters are made lower-case using LOWER before being passed to ENDSWITH, the comparison is case-insensitive, meaning that TRUE is returned.

Use CONTAINS to determine if a text string contains another text string and STARTSWITH to determine if a text string starts with another text string. Use REGEXMATCH to perform more complex text comparisons.

This function is not commonly supported by spreadsheets.

ENDSWITH and arrays

This function also works with arrays:

ENDSWITH ({ "sales@example.com", "support@example.com" }, "@example.com")

ENDSWITH ({ "sales@example.com"; "support@example.com" }; "@example.com")

This formula returns the array { TRUE, TRUE }{ TRUE; TRUE }, because both text strings end with "@example.com".

To determine if all text strings end with another text string, use the AND function:

AND ( ENDSWITH ({ "sales@example.com", "support@example.com" }, "@example.com"))

AND ( ENDSWITH ({ "sales@example.com"; "support@example.com" }; "@example.com"))

This formula returns TRUE. When AND is applied to an array, it returns TRUE only if all array elements are TRUE. As that is the case for { TRUE, TRUE }{ TRUE; TRUE }, AND returns TRUE.

Use OR to determine if at least one text string of an array ends with another text string:

OR ( ENDSWITH ({ "test@example.com", "sales@example.com", "support@example.com" }, "@example.com"))

OR ( ENDSWITH ({ "test@example.com"; "sales@example.com"; "support@example.com" }; "@example.com"))

This formula returns TRUE, even though "test@example.com" does not end with "@example.com". OR is given the logical array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, returned by ENDSWITH, and returns TRUE because at least one element is TRUE.

Use SIZE and FILTER to determine the number of text strings ending with another text string:

SIZE ( FILTER ({ "test@example.com", "sales@example.net", "support@example.net" }, ENDSWITH (Element, "@example.net")))

SIZE ( FILTER ({ "test@example.com"; "sales@example.net"; "support@example.net" }; ENDSWITH (Element; "@example.net")))

Above, FILTER is given the array { "test@example.com", "sales@example.net", "support@example.net" }{ "test@example.com"; "sales@example.net"; "support@example.net" } and returns an array holding only the elements ending with "@example.net", that is, { "sales@example.net", "support@example.net" }{ "sales@example.net"; "support@example.net" }. Finally, SIZE counts the number of elements of that array, yielding the result, 2.

Examples

ENDSWITH ("support@example.com", "@example.com")

ENDSWITH ("support@example.com"; "@example.com")

Returns TRUE. The comparison is case-sensitive.

ENDSWITH ( LOWER ("TESTING"), LOWER ("ing"))

ENDSWITH ( LOWER ("TESTING"); LOWER ("ing"))

Returns whether "TESTING" ends with "ing", when the comparison is case-insensitive. The comparison is case-insensitive because the LOWER function is used to make both parameters lower case before being passed to ENDSWITH.

ENDSWITH ({ "sales@example.com", "support@example.com" }, "@example.com")

ENDSWITH ({ "sales@example.com"; "support@example.com" }; "@example.com")

Returns the array { TRUE, TRUE }{ TRUE; TRUE }, because both text strings end with "@example.com".

AND ( ENDSWITH ({ "sales@example.com", "support@example.com" }, "@example.com"))

AND ( ENDSWITH ({ "sales@example.com"; "support@example.com" }; "@example.com"))

Returns TRUE, because all text strings end with "@example.com". AND is given the array { TRUE, TRUE }{ TRUE; TRUE } from ENDSWITH, and returns TRUE, as all array elements are TRUE.

OR ( ENDSWITH ({ "test@example.net", "support@example.com" }, "@example.com"))

OR ( ENDSWITH ({ "test@example.net"; "support@example.com" }; "@example.com"))

Returns TRUE, because at least one text string ends with "@example.com". OR is given the array { FALSE, TRUE }{ FALSE; TRUE } from ENDSWITH, and returns TRUE, as at least one array element is TRUE.

SIZE ( FILTER ({ "ab", "cb", "ef" }, ENDSWITH (Element, "b")))

SIZE ( FILTER ({ "ab"; "cb"; "ef" }; ENDSWITH (Element; "b")))

Returns 2, as two array elements ("ab" and "cb") end with "b". FILTER is asked to filter the array { "ab", "cb", "ef" }{ "ab"; "cb"; "ef" } using its second parameter. FILTER processes every array element in turn, passing it to its second parameter under the Element name, expecting to receive a logical value back which determines if the element should be included in the result array. The second parameter, ENDSWITH (Element, "b") ENDSWITH (Element; "b") invokes ENDSWITH, which only returns TRUE if the array element given to it ends with "b". As a result, FILTER returns the array { "ab", "cb" }{ "ab"; "cb" }, containing two elements. Finally, SIZE counts the number of elements, returning the number 2.