Excel Exercises Practice Name

Excel FIND Function

The FIND function in Excel tells you the location, as a number, of a smaller string (or substring) of text within a larger string of text. For example, say you want to know where the substring "foot" occurs within the string "Bigfoot". The FIND function would return 4 because the substring you're looking for ("foot") starts at the 4th letter of the larger string ("Bigfoot"). In Excel, the first letter of a string of text is considered to be position 1. If the substring does not appear in the larger string at all, the FIND function returns an error.

The FIND function in Excel is very similar to the SEARCH function, with a few important differences:

  1. FIND is case sensitive, while SEARCH is not. FIND treats "Foot" and "foot" as two different substrings. SEARCH treats them as the same.
  2. FIND does not accept wildcards while SEARCH does.

Due to these differences, the FIND function may be better suited to searching for very specific text when you know exactly what you're looking for, while SEARCH may be a better function when you're looking for a more flexible match.


Excel FIND Function Syntax

The FIND function has two required arguments and one optional argument. Each argument can be typed directly into the function or can be a reference to another cell in Excel.

=FIND(substring, string, [start_num])
  • substring - this is the text you are looking for. In the example above, this is "foot".
  • string - this is the string of text you are searching in. In the example above, this is "Bigfoot".
  • start_num - this argument is optional and specifies which position FIND should start looking at. If excluded, this argument will default to 1 and FIND will start searching at the first letter of the string. For example, if you supply a 3 here, FIND will start looking for the substring at position 3 of the string. If the substring occurs at position 2 of the text, the FIND function with this argument will not find it.


Examples With the FIND Function in Excel

Here are some examples of the FIND Function in Excel.

A
1 Bigfoot
2 Wolfman
3 Dracula
4 We love Excel!
=FIND("foot", A1)

As stated above, this formula would return 4 because "foot" starts at the 4th letter of "Bigfoot".

Bigfoot
1234567

=FIND("Foot", A1)

This formula would return an error. Why is that? Remember that the FIND function is case sensitive, and "Foot" with a capital "F" does not occur anywhere in the string "Bigfoot".


=FIND("Wolfman", A2)

This formula would return a 1 because the substring is the same as the text we're searching, so the substring begins at letter 1 of the text.


=FIND("E", A4)

This formula would return 9 because the capital "E" is the 9th letter of the string "We love Excel!".

We love Excel!
123456789

What If the Substring Occurs Multiple Times Within the Search Text?

We now know how the FIND function works in Excel, but what happens if the substring occurs more than once in the search text? Take the following example:

=FIND("a", "Dracula")

The substring "a" occurs twice in the longer string "Dracula". See below:

Dracula
1234567

What happens now? The substring is at position 3 and at position 7! In cases like this, FIND returns the first occurrence of the substring within the larger string of text, so we would get a return value of 3. But what if you don't necessarily want the first position of the substring? In this case you can utilize that third, optional, argument: start_num.

Using the FIND Function with start_num in Excel

We saw in the example above that the substring "a" appears twice in "Dracula" so by default, Excel will return the location of the first occurrence when you use the FIND function to locate the substring, which is 3. But say you only want to look for the substring at and after position 4. You could write a formula like the following:

=FIND("a", "Dracula", 4)

In this case, we start searching at letter 4 and move towards the end. FIND will still return the position of the first matching substring it finds, but it will skip all positions before 4.

Dracula
1234567

Because we only start searching at position 4, the first occurrence of "a" we find is at position 7, so FIND returns 7. Let's try another example.

=FIND("iss", "Mississippi")

This FIND formula returns 2 because the first occurrence of "iss" starts at the second letter of "Mississippi".

Mississippi
123456789...

But what if you start searching only position 3 and beyond?

=FIND("iss", "Mississippi", 3)

In this case, the FIND function would return 5, because the first "iss", if we start searching at position 3, starts at position 5.

Mississippi
123456789...

Errors With the FIND Function in Excel

There are two common cases where you might get an error with the FIND function. The first is when the substring does not exist in the string you are searching. For example:

=FIND("u", "Wolfman")

Because the substring "u" does not exist in the string "Wolfman" Excel will give you a #VALUE error.

=FIND("o", "Wolfman", 3)

This formula would also return an error because, while the substring "o" does exist in the string "Wolfman", we start searching at position 3. Because FIND can't find the substring in the part of the string it's supposed to search, it will return an error.

A second cause of an error with the FIND function may be that the optional start_num argument is an invalid number. This number must be between 1 and the length of the string of text.

=FIND("o", "Bigfoot", 8)

This function returns a #VALUE error because 8 is too big - there are only 7 positions in the string "Bigfoot".

=FIND("W", "Wolfman", 0)

This function also returns a #VALUE error, this time because the start_num argument is too small. Remember that in Excel the first letter of a string of text is position 1, so it doesn't make any sense to input position 0 as an argument.



Continue to FIND practice exercises! →