Excel Formulas and Function, Finding Text Within Another Text, Find()

Jul 12, 2015 by azurous in Excel

The function FIND() searches for a specific text expression inside another text and returns the first index which the text was found. You can download the workbook related to this article here.

Jump To:

Syntax
Example 1
Example 2

Contents

Syntax:

=FIND(Text_to_Find, Text_to_Search_in, Starting_Index)

Text_to_Find: The text to find.

Text_to_Search_in: The text to search for the “Text_to_Find” expression.

Starting_Index: The index in the Text_to_Search_in to start searching for the Text_to_Find. If omitted, the search will start at the index 1.

–

Example 1:

In the example below the formula in cell c2 searches the expression in cell A2 within the text in cell B2. If the text is found the starting index of the first occurrence is returned in cell C2. If its not found #VALUE! is returned:

Result: The text “The” is found at the start of the text in cell B2:

If the value in cell A2 is changed to “here”, cell C2 will return the value 61, which is the index the expression “here” is found in cell B2:

If we put an expression in cell A2 that does not exist in cell B2, cell C3 will return #VALUE!:

–

Example 2:

Lets assume in the previous example we wanted to find all the occurrence of the expression in cell A1, in cell B2. These are the formulas we would use:

Cell C2: Use this formula in Cell C2

=IF(ISNUMBER(FIND(A2, B2, 1)), FIND(A2, B2, 1), "")

ISNUMBER() checks if the input expression is numeric or not. If FIND(A2, B2, 1) returns #VALUE! then ISNUMBER() returns False. If FIND(A2, B2, 1) returns a number then ISNUMBER() returns TRUE.

If ISNUMBER(FIND(A2, B2, 1)) returns true, meaning that the expression FIND(A2, B2, 1) returns a number then the IF function will return the value FIND(A2, B2, 1) in cell C2. Otherwise if ISNUMBER(FIND(A2, B2, 1)) returns False meaning that FIND(A2, B2, 1) equates to #VALUE! then the IF function returns an empty cell (“”).

Cell C3: Use this formula in Cell C3

=IF(ISNUMBER(FIND(A$2, B$2, 1+C2)), FIND(A$2, B$2, 1+C2), "")

Since this formula will be copied to the cells C4, C5, … the dollar sign was used. I have passed 1 +C2 as the last parameter to the function FIND():

=IF(ISNUMBER(FIND(A$2, B$2, 1+C2)), FIND(A$2, B$2,1+C2), "")

The last parameter to the function FIND(), defines the starting location for the function to start its search. Cell C2 searches for the text in cell A2 in cell B2 starting at the index “1”. Had we omitted the last parameter, Cell C3 would have also started searching for the text in cell A2 in cell B2 starting from the index “1”, therefore returning the same result. We want cell C3 to return the next occurrence of the text in cell A2 in cell B2. This was achieved by passing the argument C2+1 as the last parameter to the function FIND(). This tells the function to start its search one index after the first occurrence of the text in cell B2.

Cell C4, C5, C6, …: Copy or drag the formula in cell C3 to all the cells under it.

By changing the value in cell A2, the values in column C will be updated automatically:

You can download the workbook related to this article here.