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:
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.
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])
Here are some examples of the FIND Function in Excel.
|4||We love Excel!|
As stated above, this formula would return 4 because "foot" starts at the 4th letter of "Bigfoot".
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".
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.
This formula would return 9 because the capital "E" is the 9th letter of the string "We love Excel!".
We love Excel!
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:
The substring "a" occurs twice in the longer string "Dracula". See below:
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.
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.
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.
This FIND formula returns 2 because the first occurrence of "iss" starts at the second letter of "Mississippi".
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.
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:
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.
The best way to become confident in Excel is to learn by doing. Reading about a new function in Excel isn't enough to learn the function - get your hands dirty and do some practice problems with the FIND function now!