This time I am detailing you the use of Google Sheets FIND function. You can use this text function the same way that we are using the SEARCH function in Google Doc Spreadsheets.
But there is ONE difference between both these text functions and what is that? I will come to that.
How to Use Google Sheets Find Function
We can use the FIND function in Google Sheets to return the position of a string that first found within a text.
Syntax:
FIND(search_for, text_to_search, [starting_at])
search_for – It’s the string that you want to search in a sentence.
text_to_search – It’s the sentence.
starting_at – at which character position the search to start.
See the examples to understand this Function.
How to Use FIND Formula in Google Sheets
Example:
Here you should pay your attention to the last two formulas. That’s the formulas in cell B7 and B8. How they differ?
The formula in Cell B7 is;
=FIND("tourist",A7,9)
Here I have used the optional part of the syntax, i.e., starting_at, since there is the repetition of the same string “tourist” twice.
See the # 9 at the end of the formula. Why this number? Because we want to find the position of the second concurrence of the string “tourist”.
So first you need to count the position of the first string and that is # 8. So you can put any number above 8 to find the position of the second string.
The formula will count the text from the first character itself. When you test this in a worksheet you can understand this.
In the example above, the formula in B8 returns an error. Why? This is because;
FIND function is case sensitive.
This is the difference of FIND formula with SEARCH formula in Google Sheets. While the former is case sensitive the latter is case insensitive.
Here is your chance to brush up your some of Google Sheets Text function usage skills
I am going to extract the word “Airport” from the sentence “I will try to pick you from the Airport”. How?
1. Directly Using the MID function.
=MID("I will try to pick you from the Airport",32,8)
2. MID and LEN Combination
=MID("I will try to pick you from the Airport",LEN("I will try to pick you from the "),8)
3. FIND and MID combination
=MID("I will try to pick you from the Airport",FIND("Airport",A19),7)
4. With REGEXEXTRACT function.
=REGEXEXTRACT("I will try to pick you from the Airport", "Airport")
In this tutorial, I’ve explained to you how to use Google Sheets FIND function. It’s similar to SEARCH and the difference you can find above.