ENDSWITH function

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

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

ENDSWITH(LOWER(TextField1), LOWER(TextField2))ENDSWITH(LOWER(TextField1); LOWER(TextField2))

This formula returns whether the value of TextField1 ends with the value of TextField2. As both parameters are made lower-case using LOWER before being passed to ENDSWITH, the comparison is case-insensitive.

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 specific to Calcapp.

ENDSWITH and arrays

This function also works with arrays:

ENDSWITH({ "sales@calcapp.net", "support@calcapp.net" }, "@calcapp.net")ENDSWITH({ "sales@calcapp.net"; "support@calcapp.net" }; "@calcapp.net")

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

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

AND(ENDSWITH({ "sales@calcapp.net", "support@calcapp.net" }, "@calcapp.net"))AND(ENDSWITH({ "sales@calcapp.net"; "support@calcapp.net" }; "@calcapp.net"))

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@calcapp.net", "support@calcapp.net" }, "@calcapp.net"))OR(ENDSWITH({ "test@example.com"; "sales@calcapp.net"; "support@calcapp.net" }; "@calcapp.net"))

This formula returns TRUE, despite the fact that "test@example.com" does not end with "@calcapp.net". 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@calcapp.net", "support@calcapp.net" }, ENDSWITH(Element, "@calcapp.net")))SIZE(FILTER({ "test@example.com"; "sales@calcapp.net"; "support@calcapp.net" }; ENDSWITH(Element; "@calcapp.net")))

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

Examples

ENDSWITH("support@calcapp.net", "@calcapp.net")ENDSWITH("support@calcapp.net"; "@calcapp.net")

Returns TRUE. The comparison is case-sensitive.

ENDSWITH(LOWER(TextField1), LOWER(TextField2))ENDSWITH(LOWER(TextField1); LOWER(TextField2))

Returns whether the value of TextField1 ends with the value of TextField2. The comparison is case-insensitive, as both parameters are made lower-case before being passed to ENDSWITH, using the LOWER function.

ENDSWITH({ "sales@calcapp.net", "support@calcapp.net" }, "@calcapp.net")ENDSWITH({ "sales@calcapp.net"; "support@calcapp.net" }; "@calcapp.net")

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

AND(ENDSWITH({ "sales@calcapp.net", "support@calcapp.net"  }, "@calcapp.net"))AND(ENDSWITH({ "sales@calcapp.net"; "support@calcapp.net"  }; "@calcapp.net"))

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

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

Returns TRUE, because at least one text string ends with "@calcapp.net". 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.