Spreadsheets applications are fantastic utilities at our disposal that not only allows us to tinker with numbers but also with text strings. So along the way, we may find ourselves needing to obtain the position of a substring within a string. We can do that with the help of the SEARCH formula in Google Sheets.
We have a close sibling to this formula called FIND. This too returns the position of a substring within a string. The only difference being that the FIND formula is case-sensitive, while SEARCH is not.
SEARCH(search_for, text_to_search, [starting_at])
- search_for – is the substring that we need to look for within text_to_search.
- text_to_search – is the main text string within which we look for the first occurrence of search_for substring.
- starting_at – [ OPTIONAL – 1 by default ] – is the position within text_to_search from which the formula starts looking for the search_for substring.
Usage: SEARCH formula in Google Sheets
Going by the syntax, the formula needs both the string and substring, as expected. But the third parameter is an optional one, in case there are multiple occurrences of the same substring. Let us now reinforce our understanding of the formula with the help of a few practical examples.
We can validate the outcomes of all the examples above by with a quick manual observation.
In the first example, we are looking for “Hello”, which incidentally happens to occur in the very first position. Hence it returned 1 for output. The second formula finds the comma (,) in the third position of the main string.
The third formula is a little interesting. Here we are looking for an empty space within the main string. However, instead of leaving out the third optional parameter, we chose to go with 10. So, the formula ignored the first two occurrences of the space character (at 5 and 9) and returned the next immediate occurrence after 10th position.
The fourth example demonstrates the capability of the SEARCH formula to ignore the case. Even if the main text is alpha-numeric in nature, the formula still works as shown in the final example.
What if there is no occurrence of a substring at all? It returns an error as illustrated in the snapshot below.