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.
The syntax of the FIND() function is as follows:
=FIND(find_text, within_text, [start_number])
A number representing the starting position of find_text in within_text.
- find_text: The text for which you want to search.
- within_text: The string containing substring that you want to search within.
- start_number: The starting position in within_text. If you omit start_number, FIND() searches for find_text from the first position of within_text.
- 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_text, FIND 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.