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)