Excel FIND Function – How to Use

The FIND function is a built-in Worksheet Function (WS) in Microsoft Excel, which you can use to locate a sub-string or a specific character's position within a text string. It is categorized as a TEXT function in Excel.

If the FIND function fails to find the text, it will return a #VALUE error. Note that the Excel FIND function will perform a case-sensitive search.

Excel FIND function is commonly used by financial analysts for locating specific data or text occurrences in a cell.

Syntax of Excel FIND function

Arguments:

'find_text' – The text/sub-string you want to locate.
'within_text' – This argument is the string within which you wish to perform the search. You can supply a cell reference or type in the string into the formula.
'start_num' – This is an optional argument wherein you specify the character from which your search must begin. If you omit this argument, the function will assume this parameter as 1, i.e., the search will begin from the 1st character of the 'within_text' string.

Things to Remember

• The FIND function in Excel is case-sensitive and does not allow the usage of wildcard characters. For locating case-insensitive matches, take a look at the SEARCH function.
• The FIND function will search the 'find_text' argument in 'within_text' and return the first character's position.
• You may search for either a substring or a character with the 'find_text'argument. You may use cell references or text characters for both 'find_text' and 'within_text' The FIND function will return '1' when the 'find_text' argument is an empty string "".
• The FIND function returns #VALUE! error when:
  • The FIND function cannot locate 'find_text'in 'within_text' or
  • The 'start_num' argument is negative, 0, or greater than the length of 'within_text'

Examples of FIND function in Excel

Example 1 – Finding a word's position in a text string

In this example, when you search for "Dallas" and reference the cell A2, which has the text string "Dallas, USA" the function will return '1'. Here, 1 represents the position of the searched word's starting point.

On account of the FIND function's case-sensitivity, entering "dallas" as an argument will return #VALUE! error.

Example 2 – Search for a word in a text string

The 'start_num' argument lets you decide the starting position for performing the search in the text string. You will see that in the above example, the FIND function returns '1' when we put 1 as the 'start_num'. Essentially, it searches for the text "Dallas" in "Dallas, USA".

When we change 'start_num' to '2', it returns an error because it then searches for "Dallas" in "allas, USA".

Note that skipping the 'start_num' argument will result in the FIND function assuming '1' as the starting position.

Example 3 – When the searched text occurs multiple times in a text string

Since the FIND function refers to the 'start_num' argument to see if you would like to define a starting position, it returns '1' when you input 'start_num' as 1. This is because it finds "Dallas" at position '1' in "Dallas, Dallas, USA".

When you input 'start_num' as 2, though, you will see that it returns '9'. What is happening here is that the FIND function tries to look for the word "Dallas" in "allas, Dallas, USA" since you are asking the function to start searching from the second position. Here, 9 is the starting position of the 2nd "Dallas" in "Dallas, Dallas, USA".

Example 4 – Look for a specific character's Nth occurrence in a string

Let's now assume that you would like to know the position of the second "," in the list that has the format "City, Country, Continent".

For this, we will need to nest two FIND functions one within another. The second FIND function will go in the first FIND function as a third argument ('start_num'), like so:

With the third argument, you are instructing the first FIND function to start searching for "," exactly after the first occurrence of a "," in the string.

Pro tip: You can use the CHAR and SUBSTITUTE functions to do this more simply, with the following formula:

Example 5 – Retrieving the first part of a text string separated by "," (comma)

Let's assume you want the list of just the name of cities, without the name of the country, i.e., the characters right before ",").

To accomplish this, we will use the LEFT function and the FIND function together. The FIND function will give us the position of "," and the LEFT function will allow us to retrieve the name of the cities.

In our example, the FIND function will return 10 when executed on "Amsterdam, Netherlands". From this, we will subtract 1 since we don't want to include the "," in our output.

Next, we embed a FIND function into the LEFT function and use FIND(",", A2,1)-1 as the second argument, like so:

Example 6 – Retrieving the second part of a text string separated by "," (comma)

Let's take example 5, and try to retrieve the second part of the string.

To accomplish this, we will use the MID function and the FIND function together. The FIND function will give us the position of "," and the MID function will allow us to fetch the specific string portion that we need.

In our example, the FIND function will return 10 when executed on "Amsterdam, Netherlands". From this, we will add 1 since we don't want to include the "," in our output.

Next, we use a MID function and pass the FIND function to it FIND(",", A2,1)+1 as the second argument, like so:

FIND function vs. SEARCH function

Both Find and Search functions have a similar syntax and application. However, there are 2 differences between these functions. Let's dive into what these differences are:

1. Acceptance of wildcard characters

Unlike with the FIND function, you may use wildcard characters in the SEARCH function's 'find_text' argument.

To match one character – we will use a question mark '?', and to match a series of characters – we will use an asterisk mark '*'.

Let's work this out with an example:

We will use the syntax:

Notice how the Excel SEARCH function returns the first character's position if you input both "," and the "continent name" regardless of how many characters exist between the text string referred to in the 'within_text'argument.

Pro tip: For finding a '?' or '*', just add a tilde (~) in front of the question mark or the asterisk.

2. FIND is case-sensitive, while SEARCH is case-insensitive

As I mentioned previously, case-sensitivity is another differentiating factor between the two functions.

In our example, when using the FIND function to search for 'A' it returns the position of the capital A in 'USA'. However, searching for 'A' with the SEARCH function returns the position of the 'a' in 'Dallas' because it is case-insensitive.

Handling #VALUE! errors in the FIND function

To deal with #VALUE! errors, we can use the IFERROR function.

Let's revisit our first example where we first encountered a #VALUE! error with FIND function on account of the FIND function's case-sensitivity.

Here is the syntax we will use to fix this:

Using this syntax, we will "trap" the error and replace it with a standard string in the second argument of the IFERROR function, which in our case is "Not Found!". So, until the FIND function is able to return a matched string, the function will keep returning "Not Found".