How to Use the Find Function in Google Sheets (An Easy Guide)

The FIND function comes in handy when you want to search for a keyword with a piece of text or string. And with enough understanding and creativity, we can take it a step further.

So today, in this simple tutorial, we will show you how to use the FIND function in Google Sheets and what else we can do with it.

Let’s get started.

Table of Contents

What is the FIND Function in Google Sheets?

Let’s say that you want to find the exact position of a letter or a keyword from a text or string in Google Sheets.

Is there a function in Google Sheets that does that?

That’d be the FIND function.

The FIND function returns the exact position of a letter or keyword found in a given string.

FIND also has the advantage of also allowing the user to optionally set the starting point of the search in the string.

Let’s now look at the breakdown and a basic example of the Find function of Google Sheets.

The FIND Function Breakdown

The syntax of the FIND function:

FIND(search_for, text_to_search, [starting_at])

Breakdown:

search_for: The letter/keyword/string that you are looking for. Must be enclosed in quotation (“”) or it can be presented as a cell reference.
text_to_search: The text or string on which the search occurs for the first occurrence of search_for. Must be enclosed in quotation (“”) or it can be presented as a cell reference.
[starting_at]: This field is optional. The starting position of the search in the text or string. Only takes integer values.

Basic Example

Let’s look at a couple of basic uses of the FIND function.

The first is:

=FIND("a","Mary")

It looks for the first occurrence of “a” in the text. We have found it in position 2.

The second is:

=FIND("had","Mary had a")

It looks for the first occurrence of “had”. This string has more characters, but the function was able to find it. The keyword starts from position 6. Note that the FIND function counts spaces as a position.

Points to Note about the FIND Function

The FIND function is case-sensitive. You must be careful when you are searching for upper- or lower-case letters or keywords.
The starting position (starting_at field) value must be an integer greater than or equal to 1. Since the first character of the text is considered to be 1.
Like many other text functions, FIND can utilize wildcards to customize search. The three wildcards are the asterisk (*), question mark (?), and tilde (~).
When a value is not found, the find function will return an error (#VALUE). So it is best to encapsulate the function inside an error handling function like IFERROR.

How to use the FIND Function in Google Sheets

Practical Example: Find a Substring from a Text in Google Sheets

Commonly, we utilize the FIND function to find the presence of a substring in a line or paragraph of text. Let’s go through this process step-by-step.

Step 1: Open the FIND function at the desired location by typing =FIND(. Google Sheets should automatically present you with a suggestion.

Step 2: Type in the letter or keyword you are looking for in quotation (“”). We have chosen the word “little”.

Step 3: Now, instead of writing out the whole sentence, we have opted to use a cell reference to our text. Our text is in cell B3.

Step 4: Close parentheses and press ENTER.

The exact match was found at position 12.

But this result may not be of any practical use unless you only want the position.

What we want is a meaningful message stating whether a match was found or not.

Applying an IF Statement with FIND Function for a Meaningful Output

To produce a meaningful output, we can simply apply an IF condition. This IF function will take advantage of ISERROR to give outputs for results found and not found (remember that no match results in an error).

Since ISERROR returns TRUE if an error (no match) is found, our formula will be:

=IF(ISERROR(FIND($E$3,B3)),"Not Found","Found")

Note: We have used absolute cell reference ($E$3) to always point to the single search criteria.

This is a huge advantage over something like the MATCH function which cannot search for keywords within a string.

When the formula cannot find the keyword:

To round off the general use of the FIND function, here are some iterative examples of how we can use it:

Similar Readings

Search for Text Over a Range Using FIND Function

It is 100% possible that you may only have a single keyword to search for in a range of cells or a column. In such cases, writing a FIND formula for each row may be impractical.

The FIND function thankfully allows us to input a range in the text_to_search field so that we can search for our keyword over the entire column.

However, on its own, FIND will only return the first result. Here is where ARRAYFORMULA comes in handy.

Our FIND formula to search over a range is:

FIND("l",B2:B5)

Enclose it in ARRAYFORMULA to present all the results. You can press CTRL+SHIFT+ENTER instead of just ENTER to apply ARRAYFORMAL around the current formula:

=ArrayFormula(FIND("l",B2:B5))

The formula is quick, can be used for any column range and you only have to use it once.

FIND vs SEARCH

Any long-time user of Google Sheets will be aware of another similar function to FIND, the SEARCH function.

It has been the topic of many discussions about any differences between them. This query generally stems from the virtually identical parameters that the FIND and SEARCH functions have.

The SEARCH function syntax:

SEARCH(search_for, text_to_search, [starting_at])

There is but only one key difference in functionality that set them apart:

The FIND function is not case sensitive whereas the SEARCH function is.

Final Words

That concludes our very simple guide on how to use the FIND function in Google Sheets. The positional value that the function returns can be utilized on its own, or it can be combined with others to give a more customized and meaningful outcome.

Feel free to leave any queries or advice you might have for us in the comments section below. Or have a look at the other search-related articles we have here.