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.

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

STEPS:

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

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

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

  • We used this formula:
=FIND(C5,B5)

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

  • We can see the #VALUE error in cell D5.

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

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

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


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.

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

  • We used the following formula:
=FIND(C5,B5,7)

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

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

Solution:

  • Replace the ‘start_num’ argument with or omit it.

Read More: Find Last Value in Column Greater than Zero in Excel


Reason 4 – The ‘start_num’ Argument Is Smaller Than or Equal to 0

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

  • Here’s the formula we used in D5:
=FIND(C5,B5,-1)

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

  • 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

  • We used AutoFill and got similar error values.

Solution:

  • Replace all the negative values with 1.

Read More: How to Find Multiple Values in Excel


Download the Practice Workbook


Related Articles


<< Go Back to Excel FIND Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo