SEARCHformula is used to find the position of a piece of text within a larger string of text. It returns the position of the first character of the searched text in the text being searched. This formula is case-insensitive, meaning it will find all instances of the searched text regardless of capitalization.
- How to use
- Examples of using
SEARCHformula not working?
- Similar formulas to
SEARCH formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- search_for (required):
The text to search for within the larger string of text.
- text_to_search (required):
The larger string of text to search within.
- starting_at (optional):
Optional parameter that specifies the character position within the text_to_search to start the search. If this parameter is not specified, the search starts at the beginning of the text_to_search.
ExamplesHere are a few example use cases that explain how to use the
SEARCHformula in Google Sheets.
Finding a keyword in a blog post
If you want to find the position of a specific keyword in a blog post, you can use the
SEARCH formula to quickly find it. Simply provide the keyword you are looking for and the text of the blog post as input to the formula.
Extracting part of a URL
If you have a list of URLs and want to extract a specific part of the URL, such as the domain name or page title, you can use the
SEARCH formula to find the position of the relevant characters in the URL and then use the
MID formula to extract them.
Searching for multiple keywords
If you want to search for multiple keywords within a larger string of text, you can use the
SEARCH formula in combination with the
IF formula to check for each keyword individually. If a keyword is found, you can return a specific value, such as "Yes", and if it is not found, return a different value, such as "No".
SEARCHnot working? Here are some common mistakes people make when using the
SEARCHGoogle Sheets Formula:
Using a number instead of a text string
SEARCH formula requires that both the
text_to_search parameters be text strings. If you accidentally input a number instead of a text string, the formula will return an error.
Forgetting to include a required parameter
text_to_search parameters are required inputs for the
SEARCH formula. If you forget to include one of these parameters, the formula will return an error.
Starting the search at the wrong character
If you specify a value for the optional
starting_at parameter that is greater than the length of the
text_to_search string, the formula will not return any results. Make sure to verify that the starting character you specify is within the range of the
The following functions are similar to
SEARCH or are often used with it in a formula:
FINDformula in Google Sheets searches for a specific text within another text and returns the position of the first occurrence of the text. This formula is often used to extract a substring from a larger string or to check if a certain text exists within another text.
REGEXMATCHformula searches for a regular expression pattern within a specified text string, and returns a boolean value of TRUE if the pattern is found, and FALSE if it is not found. This formula is commonly used to check if a string matches a certain pattern or to extract specific information from a text string based on a pattern.
The MID formula in Google Sheets is used to extract a specific number of characters from a string, starting at a specified point. This is useful when working with large datasets and you only need a portion of the information in a cell. The formula takes in the string to extract from, the starting point, and the length of characters to extract as arguments.
LEFTformula is used to extract a specific number of characters from the beginning of a text string. It is most commonly used to extract the first name or last name from a full name or to extract a date from a text string.
RIGHTformula in Google Sheets returns the rightmost characters from a string, based on the number of characters specified. It is commonly used to extract a certain number of characters from the end of a string. The formula takes two arguments: the string from which to extract the characters, and the number of characters to extract.
You can learn more about the
SEARCH Google Sheets function on Google Support.