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.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.