The FIND function in Google Sheets is useful if you want to return the position at which a string was found within the text.
Table of Contents
The FIND function in Google Sheets is similar to the
SEARCH function, with a slight difference. But will come to that later 🙂
Let’s take an example.
Say you have a text in the cell and need to know the exact position of the keyword or letter in the text.
So how do we do that?
FIND function needs the string you’re looking for and the text to search within.
Optionally, if you’re not looking for the first concurrence of the string, you can add the character position at which the search starts. If you do not add the search position, the search will automatically start from the first character.
Let’s go straight into real examples where we will deal with actual values to better understand the FIND function in Google Sheets and see how you can write it yourself.
The Anatomy of the FIND Function
The syntax (the way we write) the
FIND function is as follows:
=FIND(search_for, text_to_search, [starting_at])
Let’s break this down to understand the syntax of the FIND function and what each of these terms means:
=the equal sign is how we begin any function in Google Sheets.
FIND()is our function. We will have to add the string we want to search for, as well as the text to search within and a character position at which the search starts.
search_foris the string you are looking for within
text_to_searchis the text within you are looking for the first concurrence of
starting_at[optional, it is 1 by default] is the character position at which the search starts.
⚠️ A few notes you should know when writing your own FIND function in Google Sheets:
- Unlike the
FINDfunction is case sensitive, so you should pay attention to the uppercase and lowercase letters.
- Wildcards (‘~’ tilde, ‘*’ asterisk, and ‘?’ question mark), used with certain Google Sheets functions to represent or replace a single or multiple other characters in functions, are not allowed with the
- The starting position is a number value that must be greater than or equal to 1 and less than or equal to the number of characters in the text you are searching within (text_to_search).
FINDformula will return an error (#VALUE!) when the string you are searching for is not found within the text_to_search.
A Real Example of Using the FIND Function
Let’s take a look at the examples below to see how to use the FIND function in Google Sheets.
The first two formulas, in cells C2 and C3, are quite simple. The only difference is that you can either enter just the cell address or the text within you are looking for the first occurrence of the string. If you do not use the optional part of the syntax (starting_at) in the formula, it will count the text from the first character itself.
If you take a look at the third and fourth formulas, in cells C4 and C5, you will see that in the fourth formula (C5), we used the optional part of the syntax (starting_at), since there is the repetition of the same string “apple” twice. To find the position of the second concurrence of the string “apple”, we should first count the position of the first one, and in this case, that is #5 (as you can see in row 4). Now, we enter the first number higher than #5 and our search will start from there.
Finally, in the last formula, in cell C6, you can see that it returns an error (#VALUE!) since, unlike the
SEARCH formula, the
FIND formula in Google Sheets is case sensitive (meaning that uppercase and lowercase letters matter) 🙂
You can also use the
FIND function to search the range of cells for the first occurrence of the string. To do this, you will need the
ARRAYFORMULA wrapper. Your formula will now look like this
=ARRAYFORMULA(FIND(search_for, range, [starting_at])
If there is no string you are searching for, the formula will return an error (#VALUE!). But if the string is found, it will return the position of the string. Let’s see what happens if we try to search for the string “apple” within a range of five cells.
You can give it a try yourself by making a copy of the spreadsheet using the link below:
How to Use the FIND Function in Google Sheets
Let’s begin writing our own FIND function in Google Sheets, step-by-step.
- First, click on any cell to make it active. You should click on the cell where you want to show your result. For this guide, I will be selecting B2.
- Then, type the equal sign ‘=’ to start off the function. After that, type the name of the function, which is ‘FIND’.
- As you start to type the name of the function, you will see that the auto-suggested box with the names of the functions that start with ‘F’ will pop-up. You can close and ignore this pop-up, or you can select the FIND function by clicking on it, just make sure you click on the right one since sometimes more functions with similar names might be there.
- After the opening bracket, you should add the string you are looking for. Make sure that search_for and text_to_search are not supplied in reverse order, or the formula will likely return an error (#VALUE!).
- Enter a comma ‘,’ to act as a separator between the string you are looking for, and the text within you will perform the search.
- Once you have added the comma, enter the text within you will perform the search. You can also enter the cell address, no need to copy/paste the text.
- You can either close the function with the closing bracket ‘)‘ or hit your Enter key, which will close the bracket on the function and immediately output the result of the formula. If you followed my steps, the result in cell B2 would be 5 since this is the first position of the string “apple” within the selected text.
- However, if you are not looking for the first concurrence of the string, and you would want to find the second position of the string “apple”, you should add the optional part of the syntax (starting_at). Enter a comma ‘,’ after the text within you will perform the search (or the cell address), and add the character position at which the search starts.
- Close the function with the closing bracket ‘)‘ or hit your Enter key, and you will see that the result in cell B3 will be 31 since this is the second position of the string “apple” within the selected text.
That’s it! You did it! You can now use the
FIND function together with the other Google Sheets formulas to create even more effective formulas that will help you with your work and save time 🙂