This guide covers everything you need to know about the Google Sheets FIND function, including its definition, syntax, use cases, and how to use it.
What is the FIND Function? How Does It Work?
The FIND function in Google Sheets returns the position at which a specific string of text is first found within another string of text. The primary purpose of the FIND function is to pinpoint the exact position of a string within a cell’s content. For instance, if you want to determine where the word “wood” first appears in the phrase “How much wood can a woodchuck chuck”, you would use the FIND function.
One crucial point to note about the FIND function is its case sensitivity. This means that it differentiates between “abc” and “ABC”. When searching for a string, it considers these as two different entries. If you need a case-insensitive function, you may want to use the SEARCH function instead.
As for how the FIND function works, it essentially scans the entire text in search of the specified string. If successful, it will return the position of the string in the form of a numerical value. If it fails to find a match, the function will return a #VALUE! error. To prevent this from disrupting your data analysis, it’s recommended to use a function like IFERROR to check for situations when there are no matches found.
FIND Syntax
The syntax and arguments for the function are as follows:
FIND(search_for, text_to_search, [starting_at])
Here, each argument has a specific role in the function:
- search_for: This is the string that the function will look for within the text_to_search. This argument is mandatory.
- text_to_search: This is the text within which the function will look for the first occurrence of search_for. This argument is also mandatory.
- starting_at: This is an optional argument. It is the character within the text_to_search at which the function will start the search. If not specified, the function will start the search from the beginning of the text_to_search.
Here are some important notes about the syntax and arguments of the FIND function:
- Be careful not to supply the search_for and text_to_search arguments in reverse order. Doing so will likely result in a #VALUE! error. This is because the order of arguments in the FIND function is different from other text functions like SPLIT and SUBSTITUTE.
- If the string you’re searching for isn’t found in the text_to_search, the function will return a #VALUE! error.
Examples of How to Use the FIND Function
Here are some practical examples of how to use the FIND function in Google Sheets:
Example #1: Finding a Specific Text in a String
Let’s say you have a list of email addresses in column A and want to find the position of the “@” symbol in each email address. You can use the FIND function like this:
=FIND(“@”, A1)
This formula will return the position of the “@” symbol in the email address in cell A1. If the email address is “example@gmail.com”, the formula will return 8, because the “@” symbol is the 8th character in the string.
Example #2: Using FIND Function in Combination with MID Function
The FIND function can be used in combination with other functions like MID to extract specific parts of a text string. For instance, if you want to extract the username from an email address (i.e., the part of the email address before the “@” symbol), you can use the following formula:
=MID(A1, 1, FIND(“@”, A1) – 1)
This formula uses the FIND function to determine the position of the “@” symbol and then uses the MID function to extract the username.
Example #3: Finding the Second Occurrence of a Specific Text
The FIND function can only find the first occurrence of a specific text. If you want to find the second occurrence, you can use the following formula:
=FIND(“a”, A1, FIND(“a”, A1) + 1)
This formula first uses the FIND function to find the first occurrence of “a” in cell A1. It then adds 1 to the result to start the search for the second occurrence after the first occurrence. If cell A1 contains the text “banana”, the formula will return 3 because the second “a” is the 3rd character in the string.
FIND: Common Mistakes & Problems
When using the FIND function in Google Sheets, there are a few common mistakes and problems that can occur. Knowing these can help you avoid them and use the function more effectively.
- Using special characters: If you’re trying to find a special character, such as an asterisk or a question mark, the FIND function may not work correctly. This is because these characters are considered wildcard characters in Google Sheets. If you need to find a special character, you should use the CHAR function instead.
- Ignoring the start_at argument: The third argument of the FIND function, start_at, is optional. However, ignoring it can lead to unexpected results. If you don’t specify a number for start_at, the function starts searching from the beginning of the text string. If you want to start searching from a specific position, you should include the start_at argument.
- Misunderstanding the returned value: The FIND function returns the starting position of the text string you’re searching for, not the text string itself. If you’re expecting the function to return the text string, you’ll be confused by the results.
Why FIND Is Not Working? Troubleshooting Common Errors
If you’ve been having trouble with the FIND function in Google Sheets, it’s likely due to a few common errors. Here we’ll cover the most typical ones, their causes, and how to fix them.
#VALUE! Error
Cause: The #VALUE! error typically appears when the FIND function cannot locate the text string you’re searching for within the cell. This error can also occur if you’re using incorrect syntax or data types.
Solution: To resolve this, double-check the cell you’re searching in for the exact text string you’re looking for. Ensure that there’s no extra space, punctuation, or difference in capitalization. Also, review the syntax of your FIND function to ensure it’s formatted correctly. This should be: FIND(search_for, text_to_search, [starting_at]).
#N/A Error
Cause: The #N/A error appears when Google Sheets cannot find the text you’re looking for. This differs from the #VALUE! error in that it is specifically tied to the use of an array formula or a function that returns an array.
Solution: To fix this, verify the array or range you’re searching within for the exact text string. As with the #VALUE! error, be mindful of extra spaces, punctuation, and capitalization. If you’re using an array formula, ensure that it’s formulated correctly.
#REF! Error
Cause: The #REF! error usually indicates that your formula references a cell that doesn’t exist, typically because you deleted a cell that was being used in your FIND function.
Solution: To resolve this, go through your formula to identify any references to cells that no longer exist. Replace those references with valid ones. If you recently deleted a row, column, or cell, you may need to undo that action or adjust your formula to refer to the correct cells.
#NUM! Error
Cause: The #NUM! error appears when the starting position for the search is less than 1 or greater than the length of the text string you’re searching within.
Solution: Correct this by ensuring the starting position in your FIND function is a number that falls within the length of your text string. For example, if your text string is ten characters long, your starting position should be a number between 1 and 10.
By understanding the causes and solutions for these common errors, you can troubleshoot and fix any issues you encounter when using the FIND function in Google Sheets.
Using FIND With Other Google Sheets Functions
Combining the FIND function with other Google Sheets functions can yield powerful results. It can help you perform complex data manipulations with relative ease. Here’s how you can use FIND with some of the other Google Sheets functions:
With IF
Usage: The IF function can be used with the FIND function to return specific values based on the presence or absence of a substring in a cell.
Example: If you want to check if the word “Google” is present in cell A1, and return “Yes” if it is and “No” if it’s not, you would use the following formula:
=IF(ISNUMBER(FIND(“Google”, A1)), “Yes”, “No”)
With MID
Usage: The MID function can be used with the FIND function to extract a substring from a cell, starting from the position of a specific character.
Example: If you want to extract all the characters after the “@” symbol in an email address located in cell A1, you would use the following formula:
=MID(A1, FIND(“@”, A1) + 1, LEN(A1))
With SUBSTITUTE
Usage: The SUBSTITUTE function can be used with the FIND function to replace specific characters or substrings in a cell.
Example: If you want to replace all occurrences of the word “Google” with “Alphabet” in the text located in cell A1, you would use the following formula:
=SUBSTITUTE(A1, “Google”, “Alphabet”, FIND(“Google”, A1))
With CONCATENATE
Usage: The CONCATENATE function can be used with the FIND function to join text strings where a specific substring is found.
Example: If you want to join the text in cell A1 and cell B1 with a “-” wherever the word “Google” is found in cell A1, you would use the following formula:
=CONCATENATE(IF(ISNUMBER(FIND(“Google”, A1)), A1&”-“&B1, A1))
These are just a few examples of how the FIND function can be used with other Google Sheets functions for data manipulation. The combinations are virtually endless, allowing you to tailor your data analysis to your specific needs.
For more details on the FIND function, check out the official documentation at the Google Docs Editors Help Center.
