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
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

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.

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

Let’s see the steps to illustrate this method.

STEPS:

  • To begin with, select cell D5. Insert the following formula in that cell:
=FIND(C5,B5)

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

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

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

  • 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.


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.

FIND Function in Excel Not Working Due to Case Sensitivity of Arguments

Let’s see the steps to perform this method.

STEPS:

  • First, select cell D5. Insert the following formula in that cell:
=FIND(C5,B5)

FIND Function in Excel Not Working Due to Case Sensitivity of Arguments

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

FIND Function in Excel Not Working Due to Case Sensitivity of Arguments

  • 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.


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.

Excel FIND Function Does Not Work When ‘start_num’ Argument Is Greater Than ‘within_text’ Argument

Let’s see the steps to perform this method.

STEPS:

  • Firstly, select cell D5. Write down the following formula in that cell:
=FIND(C5,B5,7)

Excel FIND Function Does Not Work When ‘start_num’ Argument Is Greater Than ‘within_text’ Argument

  • Next, press Enter.
  • So, we get the #VALUE error in cell D5.

Excel FIND Function Does Not Work When ‘start_num’ Argument Is Greater Than ‘within_text’ Argument

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.


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.

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

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

STEPS:

  • In the beginning, select cell D5. Input the following formula in that cell:
=FIND(C5,B5,-1)

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

  • 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.

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

  • 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.


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.

Tags:

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo