A spreadsheet is a fantastic tool that allows you to tinker with numbers and also with text strings. When working with a lot of text in a spreadsheet, there are many situations where you may find yourself needing to obtain a specific word, letter, or number from a longer piece of text.
You can do that with the help of the SEARCH function in Google Sheets. It works by obtaining the position of a substring within a text string.
There is a close relative of the search function called FIND. This also returns the position of a substring within a string. The only difference is that the FIND function is case-sensitive, while SEARCH is not. So if you’re working with data where the capitalization of the text does not matter, or you want to search for text that may be either upper or lower case, then the SEARCH Google Sheets function is for you!
Syntax
=SEARCH(search_for, text_to_search, [starting_at])
- search_for – is the substring that you need to look for within the text_to_search.
- text_to_search – is the main text string within which you look for the first occurrence of the search_for substring.
- starting_at – [ OPTIONAL – 1 by default ] – is the position within the text_to_search from which the function starts looking for the search_for substring.
How to use SEARCH Google Sheets
As you can see from the syntax, this function needs both the string and substring, as expected.
But the third parameter, [starting_at], is optional. You only need to use this if there are multiple occurrences of the same substring.
I’ll show you a few examples to help you get a better understanding of the SEARCH function.
Take a look at the screenshot below and then I’ll talk you through each example.
Row 2
Here I’m looking for “Hello”, which incidentally happens to occur in the very first position. As a result, the function returns 1 as the output when I input the formula =Search(“Hello”,”Hello, there!”). As you can see from this example, the SEARCH function identifies the position where the substring starts: the word “Hello” occupies the first 5 characters in the text, but the function tells you where it starts – the very first character in the string.
Row 3
The second function finds the comma (,) in the third position of the main string. The formula here uses cell referencing instead of typing the text directly: =Search(B3,A3).
Row 4
The third function is quite interesting. Here I am looking for an empty space within the main string. However, instead of leaving out the third optional parameter, I chose to enter 10 as the starting_at parameter. As you can see, the function ignored the first two occurrences of the space character (at 5 and 9) and returned the next immediate occurrence after the 10th position. The formula I typed here is =Search(” “,A4,10).
Row 5
The fourth example demonstrates the capability of the SEARCH function to ignore the case (upper or lower) of the text. This is the difference between this function and the FIND function.
Row 6
The last example shows that even if the main text is alpha-numeric in nature, the function still works.
What if the substring does not occur at all in the text? The function returns an error, as illustrated below:
That’s how to get started with the SEARCH function in Google Sheets. Looking for more spreadsheet tips and tutorials? Check out our other Google Sheets guides below.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
