The Excel FIND function

If you use Excel often, you’re already aware of its numerous useful functions. Many of these formulas perform calculations or analyze data. However, if you want to make life easier when working with large data sets, you should know about the FIND function. What is the syntactical structure? How do you use the function? And what’s the difference between FIND and FINDB? You can find this out and more in the following article.

It’s easy to lose track of things when working in large spreadsheets with hundreds of rows. Like all other Office products, Excel has a built-in search function. But that isn’t always what you actually need. The search function automatically searches the entire document, meaning you can’t narrow down the range. More importantly, you can’t use the returned values in other functions. You can’t forward the search result because the search function operates only on the interface.

But if you want to search within specific cells and integrate the search directly into your worksheet, you can use the FIND function. Simply enter the word or phrase and the function to find the location of the first occurrence of your search term in the text string (meaning the text in the cell).

The FIND function in Excel is a building block with a range of further applications. 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.

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?

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

You can use the parameters to specify various information:

• Find_text: This is the text string you want to find. You have to enclose the text in quotes. You can also specify the cell containing the text. This parameter is always case-sensitive.
• Within_text: This parameter specifies the text within which you want to search. You typically specify a cell containing the text. You could also type the text directly into the formula. In that case, you also have to enclose it in quotes.
• Start_num: You use a numerical value to specify the character where the search is to start. This parameter is optional. If you don’t enter this value, the search will start at position 1.

Excel returns a number as the result. This value tells you where the search string begins, meaning the first occurrence from left to right. If the term occurs again in the cell, the FIND function will disregard it by itself. To find further positions, you have to use nesting. The returned value counts every single character, including spaces. The number indicates the position of the first character of the returned string. This means that the first letter or number of the search is counted in the result.

Besides FIND, Excel also offers the FINDB function. Both functions achieve the same results and have identical syntax. 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 characters from Chinese, Japanese and Korean (CJK), use FINDB because it supports double-byte character sets (DBCS) for these languages. Each character is encoded in two bytes, so counting is adjusted accordingly.

For many users, the purpose of this function is not always immediately apparent. Finding the position of a search term within a text doesn’t seem all that useful at first. But the true power of this function is revealed when used in combination with other functions.

Suppose we want to find the second, third or nth occurrence of the search term rather than the first.

This formula shows how you can use the optional third parameter. In the start_num position of this formula, we once again insert the formula that returns the position of the first occurrence. This value plus one indicates the position at which you want the search to begin. If you then want to find a third position, nest the function again, and so on.

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?

Product codes can be very long and confusing, so you may want to extract a certain number of characters from a string. Excel provides three functions for this purpose: LEFT, RIGHT and MID. These are very useful on their own, but the formulas are even more powerful when combined with FIND. Suppose your product codes always follow a specific pattern consisting 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. These functions require a specific number of characters, which you can’t easily provide in this case. But thanks to the hyphens, you can use the FIND function. It gives you the position information 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 characters.

Product codes can be very long and confusing, so you may want to extract a certain number of characters from a string. Excel provides three functions for this purpose: LEFT, RIGHT and MID. These are very useful on their own, but the formulas are even more powerful when combined with FIND. Suppose your product codes always follow a specific pattern consisting 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. These functions require a specific number of characters, which you can’t easily provide in this case. But thanks to the hyphens, you can use the FIND function. It gives you the position information 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 characters.

However, if the length is not defined, further nesting of FIND functions can be helpful. Since the string you want to find ends in a hyphen, you can search for it to determine the length.

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 also have to use the ISERROR function.