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.
- How to use
- Examples of using
FINDformula not working?
- Similar formulas to
FIND formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- search_for (required):
The text you want to search for within the other text.
- text_to_search (required):
The text or cell reference where you want to search for the specified text.
- starting_at (optional):
The position within the text_to_search where you want to start the search. If omitted, the search starts at the beginning of the text_to_search.
ExamplesHere are a few example use cases that explain how to use the
FINDformula in Google Sheets.
Extracting a substring
By using the
FIND formula, you can extract a substring from a larger string by specifying the starting position and the length of the substring.
Checking if a text exists
You can use the
FIND formula to check if a certain text exists within another text. If the formula returns a value greater than zero, the text exists and the formula can be used to perform additional calculations or return a specific value.
Parsing data from a URL
FIND formula can be used to parse data from a URL, such as extracting the domain name or query parameters.
FINDnot working? Here are some common mistakes people make when using the
FINDGoogle Sheets Formula:
Not using quotation marks for search_for parameter
FIND formula requires the search_for parameter to be enclosed in quotation marks. If the search_for parameter is not enclosed in quotation marks, the formula will return an error value.
Specifying starting_at parameter incorrectly
The starting_at parameter for the
FIND formula must be a number that represents the position within the text_to_search parameter to start the search. If the starting_at parameter is specified as a text value or is greater than the length of the text_to_search parameter, the formula will return an error value.
The following functions are similar to
FIND or are often used with it in a formula:
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.
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.
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.
LENformula in Google Sheets is used to count the number of characters in a given text string. It is commonly used to check the length of text inputs, or to find the length of a cell value or range.
You can learn more about the
FIND Google Sheets function on Google Support.