This guide covers everything you need to know about the Google Sheets SEARCH function, including its definition, syntax, use cases, and how to use it.
What is the SEARCH Function? How Does It Work?
The SEARCH function in Google Sheets returns the position at which a specific string of characters (known as the substring) is first found within a larger body of text.
One of the key aspects of the SEARCH function is its ability to start the search at a specified position within the text. By default, the search starts at the first character, but you can modify it to start at any other character position. This feature can be immensely useful when dealing with large bodies of text where you need to skip certain portions.
While the function will provide the position of the substring, it does not handle situations where there are no matches to the search. In such cases, using it in conjunction with an error-checking function like IFERROR is recommended to manage these instances effectively.
SEARCH Syntax
The syntax and arguments for the function are as follows:
SEARCH(search_for, text_to_search, [starting_at])
Here’s a breakdown of each argument:
- search_for: This is the string you are looking for within the text_to_search. This argument is mandatory.
- text_to_search: This is the text within which you search for the first occurrence of search_for. This argument is also mandatory.
- starting_at: This is an optional argument. By default, it is set to 1. It represents the character within text_to_search at which to start the search.
Now, let’s go through some important notes regarding the syntax and arguments of the SEARCH function:
- The SEARCH function is not case-sensitive. This means that it doesn’t differentiate between uppercase and lowercase letters. For example, searching for “abc” will also match “ABC”. If you need to perform a case-sensitive search, use the FIND function instead.
Examples of How to Use the SEARCH Function
Here are some practical examples of how to use the SEARCH function in Google Sheets:
Example #1: Finding a Specific Word in a Text String
Suppose you have a cell A1 with the text “Google Sheets is a powerful tool.” and want to find the position of the word “powerful”. You can use the SEARCH function as follows:
=SEARCH(“powerful”, A1)
The function will return 18, which is the position of the first letter of the word “powerful” in the text string.
Example #2: Case-Insensitive Search
The SEARCH function is case-insensitive, meaning it treats lowercase and uppercase characters as the same. For instance, if you have a cell A1 with the text “GOOGLE SHEETS” and you want to find the position of the word “sheets”, you can use the SEARCH function as follows:
=SEARCH(“sheets”, A1)
The function will return 8, which is the position of the first letter of the word “sheets” in the text string, ignoring the case difference.
Example #3: Using Wildcards
The SEARCH function supports wildcard characters, which can be useful when you don’t know the exact word or phrase you’re looking for. For example, if you have a cell A1 with the text “The quick brown fox jumps over the lazy dog.” and you want to find the position of a word that starts with “f” and ends with “x”, you can use the SEARCH function as follows:
=SEARCH(“f*x”, A1)
The function will return 16, which is the position of the word “fox” in the text string.
Example #4: Finding the Position of a Specific Character
You can also use the SEARCH function to find the position of a specific character in a text string. For instance, if you have a cell A1 with the text “hello@world.com” and you want to find the position of the “@” character, you can use the SEARCH function as follows:
=SEARCH(“@”, A1)
The function will return 6, which is the position of the “@” character in the text string.
Example #5: Searching from a Specific Position
The SEARCH function allows you to specify the position to start the search from. For example, if you have a cell A1 with the text “The cat and the hat.” and you want to find the position of the second occurrence of the word “the”, you can use the SEARCH function as follows:
=SEARCH(“the”, A1, 10)
The function will return 14, which is the position of the second occurrence of the word “the” in the text string, starting the search from the 10th character.
Why Is SEARCH Not Working? Troubleshooting Common Errors
If you find that the SEARCH function is not working as expected in Google Sheets, it may be due to a variety of errors. These errors can stem from a range of issues, from incorrect syntax to problems with the data you’re working with. Here are some common errors that you might encounter, along with their causes and solutions.
#VALUE! Error
Cause: The #VALUE! error is typically caused by an issue with the function’s input or arguments. This might happen if the text or substring you’re searching for isn’t formatted correctly or if the start number isn’t a positive integer.
Solution: Double-check your inputs to ensure they’re in the correct format. The text and substring should be enclosed in quotation marks, and the start number should be a positive integer. If you’re referencing cells, make sure the cells contain the correct data types.
#N/A Error
Cause: The #N/A error usually occurs when the SEARCH function can’t find the substring within the text. This might be because the substring doesn’t exist in the text or because the start number is greater than the length of the text.
Solution: Verify that the substring exists within the text and that the start number isn’t greater than the length of the text. You might need to adjust the substring or start number to ensure the function can find a match.
#REF! Error
Cause: The #REF! error is generally caused by an invalid cell reference. This can happen if you’re trying to reference a cell that doesn’t exist – for example, it might be a cell outside the boundaries of the spreadsheet.
Solution: Check your cell references to ensure they’re valid. If you’re referencing cells outside the current sheet, include the sheet name in the reference.
#NUM! Error
Cause: The #NUM! error typically appears when the start number is less than 1 or greater than the length of the text. This is because the SEARCH function can’t search before the first character or beyond the last character of the text.
Solution: Adjust the start number to fall within the range of 1 to the length of the text. You can use the LEN function to find the length of the text if necessary.
#ERROR! Error
Cause: The #ERROR! error is a general error that can be caused by various issues. This might occur if there’s a problem with the syntax of the function or an issue with the spreadsheet itself.
Solution: Review the syntax of the SEARCH function to ensure it’s correct. If the error persists, try reloading the spreadsheet or using the function in a new spreadsheet to see if the issue is with the spreadsheet itself.
Using SEARCH With Other Google Sheets Functions
Combining the SEARCH function with other Google Sheets functions can enhance its utility and allow for more complex data manipulation. Below are a few examples of how to use SEARCH in conjunction with other functions.
With IFERROR
The IFERROR function is useful when combined with SEARCH as it allows the formula to return a specific value if the SEARCH function results in an error. This can be particularly useful when searching for a string that may not be present in every cell.
Example:
Assume we have a list of email addresses in column A, and we want to find out which ones are from a specific domain, say “gmail.com”. We can use the SEARCH function combined with IFERROR to return “Yes” if the domain is found and “No” if not. The formula would look like this: =IFERROR(SEARCH(“gmail.com”, A1), “No”, “Yes”).
With ISNUMBER
The ISNUMBER function can be used with SEARCH to determine if the result of the SEARCH function is a number. This can be used to check if a certain string exists within a cell.
Example:
Assume we have a column of text data in column A, and we want to determine if the word “Google” appears in each cell. We can use SEARCH in combination with ISNUMBER to return TRUE if “Google” is found and FALSE if not. The formula would look like this:
=ISNUMBER(SEARCH(“Google”, A1))
With LEN
The LEN function can be used with SEARCH to determine the length of the string before a specific character or substring.
Example:
Assume we have a column of email addresses in column A, and we want to extract the username before the “@”. We can use SEARCH in combination with LEFT and LEN to achieve this. The formula would look like this:
=LEFT(A1, SEARCH(“@”, A1) – 1)
With MID
The MID function can be used with SEARCH to extract a substring from a cell starting at the position where a specific string is found.
Example:
Assume we have a column of URLs in column A, and we want to extract the domain name. We can use SEARCH in combination with MID to achieve this. The formula would look like this:
=MID(A1, SEARCH(“://”, A1) + 3, SEARCH(“/”, A1, SEARCH(“://”, A1) + 3) – SEARCH(“://”, A1) – 3)
For more details on the SEARCH function, check out the official documentation at the Google Docs Editors Help Center.
