FIND function in Excel explained

If you work a lot with Microsoft Office, you will know how useful the numerous Excel functions can be. To simplify handling large data col­lec­tions, it helps to be familiar with the FIND function.

Excel FIND function quick guide

1. Select any empty cell in your Excel document.
2. Type =FIND(search text, text), passing the text to be searched as the first parameter and the cell or text to be searched as the second parameter.

What is the Excel FIND formula used for?

It’s easy to lose track of things when working in large spread­sheets with hundreds of rows. While Excel, like all other Office products, includes a built-in search function, it isn’t always the ideal solution since it searches the entire document by default.

If you want to search within specific cells and integrate the search directly into your worksheet, you can use the FIND function. Combine FIND with other functions to unlock its full potential. For example, you can use it to see whether a certain term appears in your worksheet or to extract specific parts of a text string.

What does the Excel FIND function syntax look like?

The syntax of FIND isn’t very complex. In the standard version, you only have to specify two arguments: What do you want to find? And where do you want to find it?

=FIND(Searchtext, Text)

You can also customise your search to start at a specific character:

=FIND(search_text;text;[first_character])

You can use the para­met­ers to specify various in­form­a­tion:

• Search_text: This is the text string you want to find. You have to enclose the text in quotes. You can also specify the cell con­tain­ing the text.
• Text: This parameter des­ig­nates the text you wish to search within. Typically, you would reference a cell con­tain­ing the text, but you can also enter the text directly into the formula. If you do so, ensure the text is enclosed in quotes.
• First_character: You use a numerical value to specify the character where the search is to start. This parameter is optional.

Excel returns a number as the result. This value tells you where the search string begins, meaning the first oc­cur­rence is from left to right.

Besides FIND, Excel also offers the FINDB function. Both functions achieve the same results and have identical syntaxes. They only differ in the character sets they support. FIND only works with single-byte character sets (SBCS). This includes the Latin alphabet. If you use Asian char­ac­ters from Chinese, Japanese and Korean (CJK), use FINDB because it supports double-byte character sets (DBCS) for these languages.

The Excel FIND function in practice

The Excel formula FIND is mainly used in com­bin­a­tion with other functions, which is where it really comes into its own.

FIND & FIND for nested groups

You can determine the second, third or nth oc­cur­rence of the search term by nesting.

=FIND(Searchtext, Text, FIND(Searchtext, Text) + 1)

This formula demon­strates the use of the optional third parameter. In the first_character position, we again insert the formula that returns the position of the first oc­cur­rence. Adding one to this value indicates where you want the search to start. To find a third position, nest the function again, and repeat as needed.

FIND & ISNUMBER for true or false state­ments

The FIND function in Excel allows you to form a true or false statement from the specified position: Does the text contain the search term or not?

=ISNUMBER(FIND("Teddy bear", B2))

The ISNUMBER function returns the value TRUE if the result of FIND is a number, otherwise it will return FALSE. Since the FIND function in Excel specifies the position of the term as a whole number, the ISNUMBER function can respond to it. If the text does not contain the search term, FIND returns an error message, which of course is not a number, and ISNUMBER responds ac­cord­ingly with FALSE.

You may also be in­ter­ested in seeing where search terms appear. You can do this if you’ve entered data in multiple cells, such as a list of products sold. You can add this formula (like any other formula) to the con­di­tion­al format­ting rules. This allows you to select any sales entry related to teddy bears, for example.

Close

FIND & MID for ex­tract­ing char­ac­ters

Product codes can be very long and confusing, so you may want to extract a certain number of char­ac­ters from a string. Excel provides three functions for this purpose: LEFT, RIGHT and MID. Suppose your product codes always follow a specific pattern con­sist­ing of letters, numbers, and hyphens: ABCDE-A-12345-T. You want to extract the numerical part in the middle.

However, because the string does not have a fixed length, you can’t use the basic extract functions. But thanks to the hyphens, you can use the FIND function. It gives you the position in­form­a­tion you need.

Since there are multiple hyphens in the string, you have to nest the FIND function. In this example, we’re assuming that the number part always contains five char­ac­ters.

=MID(A2, FIND("-", A2, FIND("-", A2, FIND("-", A2) + 1) + 1) + 1, 5)

Close

If the length is not specified, you can use ad­di­tion­al nested FIND functions. To determine the length of the string you want to find, search for the hyphen at the end of the string.

=MID(A2, FIND("-", A2, FIND("-", A2, FIND("-", A2) + 1) + 1) + 1, FIND("-", A2, FIND("-", A2, FIND("-", A2) + 1) + 1) - FIND("-", A2, FIND("-", A2, FIND("-", A2) - 1)) - 3)

Ad­mit­tedly, this formula is very confusing, but it actually manages to achieve the goal. No matter how many char­ac­ters you place between the two hyphens, Excel will always extract the correct char­ac­ters using the FIND function.

Close

FIND & IF for if, then, otherwise

You can also easily combine FIND with IF. For example, you may want a certain action to happen if a specific string occurs in the cell. You can do this by combining IF and FIND. If the string appears, this will happen, otherwise that will happen. A problem may arise if FIND returns an error if the string does not appear. Therefore, you have to use the ISERROR function in addition.

=IF(ISERROR(FIND("bear",A2)),"No","Yes")

If the FIND function doesn’t find the search term (‘bear’ in this example), it returns an error message. For ISERROR, this means the condition is met and IF returns the first option: No, the search term does not appear. However, if the FIND function finds the string, it returns a number, which does not meet the condition for ISERROR. The al­tern­at­ive is returned: Yes, the search term appears.

Close