How to correct a #VALUE! error in FIND/FINDB and SEARCH/SEARCHB functions

This topic provides help for the most common scenarios for the #VALUE! error in the FIND/FINDB and SEARCH/SEARCHB functions.

A few things to know about FIND and SEARCH functions

• The FIND and SEARCH functions are very similar. They both work in the same way - locate a character or a text string in another text string. The difference between these two functions is that FIND is case-sensitive, and SEARCH is not case-sensitive. So if you don't want to match case in a text string, use SEARCH.

• If you want a function that returns the string based on the character number you specify, use the MID function along with FIND. You can find information and examples of using MID and FIND combinations in the FIND help topic.

• The syntax of these functions is the same, find_text, within_text, [start_num]). In simple English, the syntax means What do you want to find?, Where do you want to find it?, What position do you want to start from?

Problem: the value in the find_text argument cannot be found in the within_text string

If the function cannot find the text to be found in the specified text string, it will throw a #VALUE! error.

For example, a function like:

• =FIND("gloves","Gloves (Youth)",1)

Will throw the #VALUE! error, because there is no matching "gloves" in the string, but there is "Gloves". Remember that FIND is case-sensitive, so make sure the value in find_text has an exact match in the string in the within_text argument.

However, this SEARCH function will return a value of 1, since it's not case-sensitive:

• =SEARCH("gloves","Gloves (Youth)",1)

Solution: Correct the syntax as necessary.

Problem: The start_num argument is set to zero (0)

The start_num argument is an optional argument, and if you omit it, the default value will be assumed to be 1. However, if the argument is present in the syntax and the value is set to 0, you will see the #VALUE! error.

Solution: Remove the start_num argument if it is not required, or set it to the correct appropriate value.

Problem: The start_num argument is greater than the within_text argument

For example, the function:

• =FIND("s","Functions and formulas",25)

Looks for "s" in the "Functions and formulas" string (within_text) starting at the 25th character (start_num), but returns a #VALUE! error because there are only 22 characters in the string.

Tip:  To find the total number of characters in a text string, use the LEN function

Solution: Correct the starting number as necessary.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

Correct a #VALUE! error

FIND/FINDB functions

SEARCH/SEARCHB FUNCTIONS

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)