FIND Function Not Working in Excel (4 Reasons with Solutions)

Overview of the FIND Function

Description

The FIND function is used to locate a particular character or substring inside a text string

Generic Syntax

FIND(find_text, within_text, [start_num])

Argument Description

Argument	Requirement	Explanation
find_text	Required	Substring that we want to find.
within_text	Required	Where the text will be searched.
[start_num]	Optional	The beginning position of search in the text. The default value of this argument is 1.

Returns

The location of a specific substring from a string.

Available in

All versions after Excel 2003.

4 Reasons with Solutions to FIND Function Not Working in Excel

Reason 1 – The ‘within_text’ Argument Does Not Contain the ‘find_text’ Argument

In the following dataset, we have some strings in cells (B5:B8). We can find the positions of substrings of cell range (b) using the FIND function. Suppose we want to find the position of the substring ‘a’ in the string Microsoft. The substring a is not present in string Microsoft. The ‘within_text’ argument does not contain the ‘find_text’ argument. The FIND function will not work in this case.

STEPS:

Select cell D5.
Insert the following formula in that cell:

=FIND(C5,B5)

Press Enter.
The above formula gives a #VALUE error in cell D5 as the string Microsoft doesn’t contain substring a.

After using AutoFill, we get the #VALUE error for each case since the substrings are not present in stings.

Solution:

Use different search terms in column C. Since the ‘within_text’ contains the newly added values, we do not get any #VALUE error.

Read More: How to Find Text in Cell in Excel

Reason 2 – Case Sensitivity of Arguments

The FIND function doesn’t work if the ‘find_text’ doesn’t match exactly with the strings of ‘within_text’. In the following dataset, we have the same dataset just with different substrings. In cell B5, the string is Microsoft. We will find the position of substring m. The substring character is in lower case while the string contains the same character in uppercase.

We used this formula:

=FIND(C5,B5)

We can see the #VALUE error in cell D5.

Using AutoFill gets a #VALUE error for each case since the substrings do not match exactly with any of the corresponding strings.

Solution:

Replace the previous values of substrings with new values that match exactly with the ‘within_text’ argument.

Reason 3 – The ‘start_num’ Argument Is Greater Than the ‘within_text’ Argument

While using the FIND function it is mandatory that the value of the ‘start_num’ argument will not be greater than the number of total characters in the ‘within_text’ argument. The FIND function in excel will not work if you input a value of the ‘start_num’ argument greater than the ‘within_text’ argument. To illustrate this method we will use the following dataset.

We used the following formula:

=FIND(C5,B5,7)

We get the #VALUE error in cell D5.

This error occurs because the position of substring M in string Microsoft is 1. But the FIND function starts looking from position 7. The function can’t find the position of M and returns the #VALUE error.

Using AutoFill returns the #VALUE error in all cases since the ‘start_num’ argument is greater than the position of that string in ‘witin_text’.