Text
The text string to check.
Comparison
The text string that the first text string may, or may not, contain.
Returns
Whether a text string contains another text string.
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:
This formula returns whether "TESTING" contains "test". As both parameters are made lower-case using LOWER before being passed to CONTAINS, the comparison is case-insensitive, meaning that TRUE is returned.
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 not commonly supported by spreadsheets.
CONTAINS and arrays
This function also works with arrays:
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:
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:
This formula returns TRUE, even though "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:
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
Returns TRUE. The comparison is case-sensitive.
Returns whether "TESTING" contains "test", 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 CONTAINS.
Returns the array { TRUE, TRUE, TRUE, TRUE }{ TRUE; TRUE; TRUE; TRUE }, because all four text strings contain "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.
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.
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.