SEARCH

The SEARCH function searches a text value for a specific piece of text, and returns the starting position of the sub-text if found.

Usage

SEARCH(substring, string, start_index)
Argument Type Required Description
substring Text Yes The text to search for
string Text Yes The text in which to search
start_index Numeric No The one-based position in the text to start searching

Result

The one-based starting position of the sub-string, if found. If the substring is not found, the result is blank.

When searching, case is ignored.

Examples

Searching for keywords

If you are collecting open-ended text in multi-line text field, you might want to determine how many respondents mention specific keywords. For example, how many respondents mentioned in "security" in their comments.

IF(ISNUMBER(SEARCH("security", COMMENTS)), 1, 0)

You can then use this as a measure in a pivot table to find out how many respondents mentioned "security" in their response. Because SEARCH is case insensitive, it will match "Security", "SECURITY", "SeCurITY" and any other string of characters that varies only by case.

You could expand this formula to search for multiple keywords, for example, "danger" as well as "security":

IF(ISNUMBER(SEARCH("security", COMMENTS)) || ISNUMBER(SEARCH("danger", COMMENTS)), 1, 0)

For more complex patterns, consider using REGEXMATCH. The formula above could be rewritten as:

IF(REGEXMATCH(COMMENTS, "security|danger", 1, 0)
Next item
NOW