FIND Function in Google Sheets

Published on

The FIND function is a case-sensitive function that returns the position at which a string is first found within a text in Google Sheets.

There is a similar function called SEARCH, which is more commonly used because it’s case-insensitive.

Similar to SEARCH, the FIND function is often used in combination with MID or for filtering rows based on partial matches.

Find Function: Syntax and Arguments

Syntax:

FIND(search_for, text_to_search, [starting_at])

Arguments:

  • search_for: The string to find within text_to_search.
  • text_to_search: The text or the cell reference containing the text in which to search for the first occurrence of search_for.
  • starting_at (optional): The character’s position within text_to_search at which to start the search.

Example:

=FIND("USD", "Pay USD 1,200.00") // returns 5
=FIND("usd", "Pay USD 1,200.00") // returns #VALUE error

To handle the error, use the IFERROR function:

=IFERROR(FIND("usd", "Pay USD 1,200.00"))

The starting_at argument requires thorough understanding to avoid errors. Please see the following formulas:

=FIND(" ", "01 123456789 123", 1)
=FIND(" ", "01 123456789 123", 2)
=FIND(" ", "01 123456789 123", 3)

All these formulas will return 3 even though the starting_at is different. This is because, when returning the position, the FIND function starts the count from the first character, not from starting_at.

The following formula returns 13 since starting_at is 4 and the first space character after this starting_at is the 13th character. Here also, the count starts from the very first character in the text:

=FIND(" ", "01 123456789 123", 4)

Using the FIND Function as a FILTER Condition in Google Sheets

The FILTER function does not support wildcards in Google Sheets. Therefore, to filter rows based on partial text matches within a column, you can use FIND or SEARCH functions. Note that FIND is case-sensitive, whereas SEARCH is not.

=FILTER(A1:A, FIND("INR", A1:A))
FIND function with FILTER function in Google Sheets

The above combination of FILTER and FIND functions filters rows where the value contains “INR”.

For more advanced pattern matching, consider using REGEXMATCH instead of FIND in Google Sheets.

MID and FIND Combo

Here is an interesting combination of MID and FIND functions in Google Sheets.

Cell A1 contains the following text:

After a long day at work, Mr. Ben relaxed with a good book

In cell B1, you can use the formula to extract the name “Ben”:

=LET(mr, FIND("Mr. ", A1)+4, fs, FIND(" ", A1, mr), n, fs-mr, MID(A1, mr, n))
  • FIND("Mr. ", A1) + 4 returns the starting point of the name in the text (named ‘mr’).
  • FIND(" ", A1, mr) returns the starting point of the text after the name (named ‘fs’).
  • fs - mr returns the number of characters in the name.
  • The MID formula, MID(A1, mr, n), returns the text starting at ‘mr’ and extracts ‘n’ characters.

If you understand this formula, you will become a proficient user of the FIND function.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

Reset SCAN by Another Column in Google Sheets and Excel

Resetting SCAN function results based on values in another column is a topic of...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

More like this

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.