This time I am detailing you the use of Google Sheets FIND function. You can use this text function the same way that we used with SEARCH function in Google Doc Spreadsheets. 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 FIND function in Google Sheets to return the position of a string that first found within a text.
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
Here you should pay your attention to the last two formulas. That’s the formulas in cell B7 and B8. How they differ?
Formula in Cell B7 is “=FIND(“tourist”,A7,9)”. Here we have used the optional part of the syntax, i.e., starting_at, since there are 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. But the formula will count the text from the first character itself. When you test this in a worksheet you can understand this.
Now the formula in B8 returns 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 on the opposite.
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”)