CONTAINS function

Returns whether a text string contains another text string. CONTAINS("Fee", "F")CONTAINS("Fee"; "F") returns TRUE, as "F" is part of "Fee".

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

CONTAINS(LOWER(TextField1), LOWER(TextField2))CONTAINS(LOWER(TextField1); LOWER(TextField2))

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

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

This function is specific to Calcapp.

CONTAINS and arrays

This function also works with arrays:

CONTAINS({ "Fee", "Fi", "Fo", "Fum" }, "F")CONTAINS({ "Fee"; "Fi"; "Fo"; "Fum" }; "F")

This formula returns the array { TRUE, TRUE, TRUE, TRUE }{ TRUE; TRUE; TRUE; TRUE }, because all four text strings contains "F".

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

AND(CONTAINS({ "Fee", "Fi", "Fo", "Fum" }, "F"))AND(CONTAINS({ "Fee"; "Fi"; "Fo"; "Fum" }; "F"))

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 }{ TRUE; TRUE; TRUE; TRUE }, AND returns TRUE.

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

OR(CONTAINS({ "Fee", "Fi", "Fo", "Fum", "Bread" }, "F"))OR(CONTAINS({ "Fee"; "Fi"; "Fo"; "Fum"; "Bread" }; "F"))

This formula returns TRUE, despite the fact that "Bread" does not contain "F". OR is given the logical array { TRUE, TRUE, TRUE, TRUE, FALSE }{ TRUE; TRUE; TRUE; TRUE; FALSE }, returned by CONTAINS, and returns TRUE because at least one element is TRUE.

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

SIZE(FILTER({ "Fee", "Fi", "Fo", "Fum", "Bread" }, CONTAINS(Element, "F")))SIZE(FILTER({ "Fee"; "Fi"; "Fo"; "Fum"; "Bread" }; CONTAINS(Element; "F")))

Above, FILTER is given the array { "Fee", "Fi", "Fo", "Fum", "Bread" }{ "Fee"; "Fi"; "Fo"; "Fum"; "Bread" } and returns an array where all elements contain "F", that is, { "Fee", "Fi", "Fo", "Fum" }{ "Fee"; "Fi"; "Fo"; "Fum" }. Finally, SIZE counts the number of elements of that array, yielding the result, 4.

Examples

CONTAINS("Fee", "F")CONTAINS("Fee"; "F")

Returns TRUE. The comparison is case-sensitive.

CONTAINS(LOWER(TextField1), LOWER(TextField2))CONTAINS(LOWER(TextField1); LOWER(TextField2))

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

CONTAINS({ "Fee", "Fi", "Fo", "Fum" }, "F")CONTAINS({ "Fee"; "Fi"; "Fo"; "Fum" }; "F")

Returns the array { TRUE, TRUE, TRUE, TRUE }{ TRUE; TRUE; TRUE; TRUE }, because all four text strings contain "F".

AND(CONTAINS({ "Fee", "Fi", "Fo", "Fum" }, "F"))AND(CONTAINS({ "Fee"; "Fi"; "Fo"; "Fum" }; "F"))

Returns TRUE, because all text strings contain "F". AND is given the array { TRUE, TRUE, TRUE, TRUE }{ TRUE; TRUE; TRUE; TRUE } from CONTAINS, and returns TRUE, as all array elements are TRUE.

OR(CONTAINS({ "Fee", "Fi", "Fo", "Fum", "Bread" }, "F"))OR(CONTAINS({ "Fee"; "Fi"; "Fo"; "Fum"; "Bread" }; "F"))

Returns TRUE, because at least one text string contains "F". OR is given the array { TRUE, TRUE, TRUE, TRUE, FALSE }{ TRUE; TRUE; TRUE; TRUE; FALSE } from CONTAINS, and returns TRUE, as at least one array element is TRUE.

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

Returns 2, as two array elements ("ab" and "cb") contain "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, CONTAINS(Element, "b")CONTAINS(Element; "b") invokes CONTAINS, which only returns TRUE if the array element given to it contains "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.