Excel FIND Function and Formula with Examples


How to Use the FIND Function and Formula in Excel

In this tutorial, you'll learn how to use the Excel FIND function

FIND is a function to get the position of one text string (find_text) within another text string (within_text) and returns the number of the starting position of find_text in within_text.

FIND() Syntax

The syntax of the FIND() function is as follows:

=FIND(find_text, within_text, [start_number])

Return Value

A number representing the starting position of find_text in within_text.

Arguments

  • find_text: The text for which you want to search.
  • within_text: The string containing substring that you want to search within.
  • start_numberThe starting position in within_text. If you omit start_numberFIND() searches for find_text from the first position of within_text.

Remarks

  • start_number is optional and defaults to 1.
  • FIND is case sensitive and doesn't allow wildcard characters.
  •  If find_text not found in within_textFIND returns the #VALUE! (error value)
  • If start_number is greater than the length of within_text, FIND returns #VALUE! 
  • If start_number is not greater than zero, FIND returns #VALUE!.

FIND() Function Examples

Example 1: Finding from the first position of the text

The following example shows how to find one text within another text from the first position:

=FIND("Tutorial", "Best Excel Tutorial")

Because the start_number argument omitted, FIND searches for Tutorial within the phrase Best Excel Tutorial from the beginning. And the formula returns 12, which is the starting position.

You can also write the above formula as follows:

=FIND("Tutorial", "Best Excel Tutorial", 1)

Example 2: Finding from a specified position

To find one text within another text from the specified position, specify the start_number, as the following example shows:

The following illustrates an example of a formula to find word Tutorial within the phrase Best Excel Tutorial, starting at the thirteenth position:

=FIND("Tutorial", "Best Excel Tutorial", 13)

The above formula searches for Tutorial within the phrase Best Excel Tutorial, starting at the thirteenth position.

FIND can not find and returns #VALUE!.

Example 3: FIND() returns the position of the first occurrence

If there is more than one word or phrase that you are searching for, FIND() returns the position of the first occurrence.
Here is an example:

=FIND("Excel", "Microsoft Excel FIND function. List of Excel functions", 3)

The above formula returns 11, which is the position of the 1st occurrence of Excel

Example 4: FIND() is case-sensitive

FIND() is case-sensitive. For example, Excel and excel are different when performing a search.

The following example shows a case-sensitive search:

=FIND("excel", "Microsoft Excel FIND function.")

Nothing found FIND() returns the #VALUE!.

In this tutorial, you've learned how to use the Excel FIND function

FIND() is a function to find the text within another text and return the position of the matched text. 

Unlike SEARCH(), FIND() is case-sensitive and doesn't allow the wildcard characters (*,?). If you need to perform a case-insensitive search, use SEARCH() instead.

Leave a Reply

Your email address will not be published. Required fields are marked *