The SEARCH function in Google Sheets is useful to return the position at which a string is first found within the text.
Table of Contents
SEARCH function does this simply by searching for the location of one text string inside another.
Let’s take an example.
Say you have a list of email addresses. You want to extract the beginning of the email addresses, so only the username part until the ‘@’ character.
So how do we do that?
SEARCH function is straightforward and easy to use. It only needs the text we search for and the text in which we search.
Let’s dive right into real examples to deal with actual values and see how to use the SEARCH function in Google Sheets.
The Anatomy of the SEARCH Function
So the syntax of the
SEARCH function is as follows:
=SEARCH(search_for, text_to_search, [starting_at])
Let’s dissect this and understand what each of these terms means:
=the equal sign is how we start every function in Google Sheets.
SEARCHis our function. We will have to add the variables into it for it to work.
search_foris a required field that represents the substring that we want to look for within the text.
text_to_searchis another required field that is the text to search for the first occurrence of
search_for. It reads from left to right, so the first occurrence means the first from left.
startis an optional field that represents the number to start the sequence. If you omit using it, the sequence will start at 1.
starting_atis an optional field that represents the index within
text_to_searchat which we want to start the search. Its default value is 1, so it starts the search at the first character of the text.
In general, the
SEARCH function needs to know what we are looking for (
search_for) and in which text we are searching (
Optionally, we can also give a starting point at which to start the search (
starting_at). This optional attribute might be useful mostly when you know that there are multiple occurrences of your
search_for value, and you want to ignore the first (few) results.
As a result, the
SEARCH function returns a number. This number represents the position of the first character of the first occurrence of the searched text.
⚠️ Now A Few Notes to Use SEARCH Function Even Better
- It’s important to note that
SEARCHis not case-sensitive, meaning that uppercase and lowercase letters do not matter. For example, “abc” will match “ABC“.
FINDfunction is a very similar function to
FINDfunction also returns the position of a substring within a string. The only difference is that the
FINDformula is case-sensitive while
SEARCHis not. To compare text where uppercase and lowercase letters matter, use the
- Make sure that you don’t add your
text_to_searchattributes in reverse order. The arguments should be supplied in a different order than other text functions such as
SPLITand other well-known text functions.
SEARCHfunction allows the use of wildcards. In
search_for, you can use an ‘*‘ (asterisk) to match multiple characters or a ‘?‘ (question mark) to match any single character in
- To find a literal ‘?‘ or ‘*‘ in the text, you should use a ‘~‘ character before the searched character, for example, ‘~*‘ and ‘~?‘.
- The start argument can’t be greater than the length of
- A #VALUE! error occurs when the given
search_foris not found in the supplied
- Similarly, a #VALUE! error occurs when
starting_atis less than zero or is greater than the length of the
A Real Example of Using SEARCH Function
SEARCH function alone is rarely used since we don’t often need the numeric position of a text fraction. Rather we use it as part of other formulas.
For example, we can combine it with other text functions (for example, the
LEFT function) and use it when finding a specific position for other functions.
Take a look at the examples below to see how we use the SEARCH function in Google Sheets.
In this example, we have a list of email addresses, and we want to extract the username (the left part) of these addresses.
We used the
LEFT function to return a substring from the beginning of a string. Check out our article on how to use the LEFT function in Google Sheets to learn more about its use!
LEFT function needs a position, and it returns the left part of a string until that specified position.
Since email addresses have a username, followed by a ‘@’ character, we used the
SEARCH function to search for these ‘@’ characters in the cells. After that, we created the attributes of the
LEFT function with the help of this result.
The function is as follows:
Here’s what this example does:
- We selected the cell where we wanted to show the result and started writing the
LEFTfunction. We started writing it in cell B2.
LEFTfunction needs two attributes: the source text we want to use and the position where we want to cut it. The source text is obviously the email address we need to cut, so the cell A2.
- After that, the position where we want to cut the email address is calculated with the help of the
SEARCHfunction. We started writing it as the second attribute.
SEARCHfunction first needs the
search_forattribute, which is the ‘@’ character.
- Then, it needs the
text_to_searchattribute, which is the original email address, so we used the cell reference A2 here.
SEARCHfunction returns a number that is the position of the ‘@’ character.
- Finally, we need to subtract 1 from this position, because we don’t want to include the ‘@’ character in our result, only the part before.
- We applied the same function to the rest of the list by dragging it down through column B.
- As a result, we get the username parts of the email addresses until the ‘@’ character.
You can give it a try yourself by making a copy of the spreadsheet using the link below:
How to Use SEARCH Function in Google Sheets
Let’s begin writing our own SEARCH function in Google Sheets step-by-step.
- To start, click on any cell to make it the active cell. For this guide, I will be selecting B2.
- Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘
search‘ (or ‘
SEARCH‘, whichever works).
- Great! Now you should find that the auto-suggest box will pop-up with the name of the functions starting with
SEARCH. Select the right function (the one that is simply
SEARCH) by clicking on it.
- After the opening bracket ‘(‘, you have to add the attributes. Remember that you can add up to three attributes, and two of them are required, the third one is optional. In this example, we want to get the position of the ‘@‘ character, so we added it as the first attribute. Make sure to put this between “” double quotation marks since it is a direct textual value here.
- After that, you need to add a comma and then the other required attribute which is the
text_to_search. Here we want to search in the email addresses, so we reference the cell with the corresponding email address. So we will be selecting the cell B2 by clicking on it.
- Hit the Enter key, and you can see the position of the ‘@‘ character in the email address! We got 7 here as a result, and indeed the seventh character is the ‘@‘.
- Drag down the function to the whole column to apply it for all the email addresses.
- Now you might use this result as an index for other text functions. Let’s see our example with the
LEFTfunction. Double click on the cell B2 to continue editing it.
- After that, click between the ‘=’ equal sign and the
SEARCHfunction and type the name of the
LEFTfunction followed by the ‘(‘ opening bracket.
- Now you should put the first attribute of the
LEFTfunction here, which is the text you want to cut. Click on the cell that has your text. For example, here I click on the cell with the right email address, so on the cell A2. Put a comma next to this attribute, and you can already see the expected result.
- Finally, there is only one more thing you need to do to extract the username. As you can see, the expected result includes the ‘@‘ character as well, which we don’t need. The position is defined by the result of the
SEARCHfunction. Consequently, if you subtract 1 from the
SEARCHformula (by writing -1 at the end of it), you will get the right result!
- Hit on your Enter key to close the whole formula and get the result! Also, apply the modified function to the whole column. Now, if you followed our steps, you can see the extracted usernames.
That’s it, good job! You can now use the
SEARCH function in combination with the other Google Sheets formulas to create even more powerful scripts that can make your life much easier. 🙂