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

In this tutorial, we will explain the reasons for the problem of why the FIND function in excel is not working. In Microsoft Excel, the FIND function is used to locate a particular character or substring inside a text string. Sometimes the FIND function doesn’t work properly and gives a #VALUE error. This error occurs as a result of incorrect argument selection in the FIND function.

Download Practice Workbook

You can download the practice workbook from here.

Overview of Excel 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

• 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

Throughout this article, we will demonstrate 4 reasons and solutions to the problem of why the FIND function in excel is not working. To illustrate this clearly to you we will use a unique dataset for each method.

Reason 1: FIND Function Not Working If ‘within_text’ Argument Does Not Contain ‘find_text’ Argument in Excel

First and foremost, we will discuss why the FIND function in excel is not working since the ‘within_text’ argument doesn’t 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 will find the position of the substring ‘a’ in the string Microsoft. If you notice the substring a is not present in string Microsoft. So, in this case, the ‘within_text’ argument does not contain the ‘find_text’ argument. The FIND function will not work in this case.

Let’s see the steps to illustrate this method.

STEPS:

• To begin with, select cell D5. Insert the following formula in that cell:

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

• Lastly, insert the following formulas of cells (E6:E8) in cells (D6:D8). We get the #VALUE error for each case since the substrings are not present in stings.

Solution:

Now to solve this error copy, the following new values of substrings 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: FIND Function in Excel Not Working Due to Case Sensitivity of Arguments

In Excel, the FIND function doesn’t work if the ‘find_text’ doesn’t match exactly with the strings of ‘within_text’. So, the case sensitivity of arguments is another reason for which the FIND function in Excel is not working. In the following dataset, we have the same dataset just with different substrings. In cell B5 the string is Microsoft. From that string, we will find the position of substring m. We can see that the substring character is in lower case while the string contains the same character in uppercase.

Let’s see the steps to perform this method.

STEPS:

• First, select cell D5. Insert the following formula in that cell:

• Hit Enter.
• Next, we can see the #VALUE error in cell D5.

• Finally, write down the following formulas of cells (E6:E8) in cells (D6:D8). We will get a #VALUE error for each case since the substrings do not match exactly with any of the corresponding strings.

Solution:

To solve this error, replace the previous values of substrings with new values that match exactly with the ‘within_text’ argument. After replacing we can see that the FIND function works properly and does not return any #VALUE error.

Read More: How to Find If A Range of Cells Contains Specific Text in Excel (4 Methods)

Similar Readings

• Excel Search for Text in Range (11 Quick Methods)
• How to Find If Cell Contains Specific Text in Excel
• How to Find Value In Range in Excel (3 Methods)
• Excel Function: FIND vs SEARCH (A Comparative Analysis)
• How to Find a Character in String in Excel

Reason 3: Excel FIND Function Does Not Work When ‘start_num’ Argument Is Greater Than ‘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.

Let’s see the steps to perform this method.

STEPS:

• Firstly, select cell D5. Write down the following formula in that cell:

• Next, press Enter.
• So, 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. That’s why the function can not find the position of M and returns the #VALUE error.

• In the end, insert the formulas of cells (E6:E9) in cells (D6:D9). We get the #VALUE error in all cases since the ‘start_num’ argument is greater than the position of that string in ‘witin_text’.

Solution:

Replace the ‘start_num’ argument with 1. This action will remove all the #VALUE errors from the dataset. The FIND function returns output because the value of the ‘start_num’ argument is now smaller than the ‘within_text’ argument.

Read More: Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)

Reason 4: FIND Function in Excel Do Not Work If ‘start_num’ Argument Is Smaller Than or Equal to 0

Another reason behind the FIND function not working in excel is the value of the ‘start_num’ argument is smaller than or equal to 0. If we input any value of ‘start_num’ argument 0 or negative the FIND function will return a #VALUE error. To illustrate this we will use the negative value of the ‘start_num’ argument in the following dataset.

So, let’s see the steps associated with this method.

STEPS:

• In the beginning, select cell D5. Input the following formula in that cell:

• Press, Enter.
• As a result, we get a #VALUE error in cell D5 since we have used a negative value -1 as the ‘start_num’ argument.

• Lastly, input the following formulas of cells (E6:E8) in cells (D6:D8). We get the #VALUE error in each cell. It happens because the value of the ‘start_num’ argument is negative in each formula.

Solution:

Since the negative value of the ‘start_num’ argument is the reason for the #VALUE error that’s why just replace all the negative values with 1. So, the FIND function doesn’t return the #VALUE error anymore.

Read More: How to Find Multiple Values in Excel (8 Quick Methods)

Conclusion

In conclusion, this tutorial will give you a clear idea of why the FIND function is not working in excel. Download the practice workbook that comes with this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to reply to you as soon as possible. So, keep an eye out for more intriguing Microsoft Excel solutions in the future.

Related Articles

• How to Find from Right in Excel (6 Methods)
• Find External Links in Excel (6 Quick Methods)
• How to Find Character in String Excel (8 Easy Ways)
• Excel Find Last Column With Data (4 Quick Ways)
• Find First Occurrence of a Value in a Range in Excel (3 Ways)
• Find the Last Cell With Value in Row in Excel (6 Methods)