# Thread: #VALUE! to be replaced (97)

1. ## #VALUE! to be replaced (97)

#VALUE! normally comes up when an error occurs. But SEARCH returns #VALUE! as a normal function, not as an error.
SEARCH returns a number representing a position if the string is found, but returns #VALUE! if not found. I expect a zero if not found. But I can't change that, so ......here is the problem:

I am checking for a string in another string and I want to have value of 1 if found, and 0 or blank if not found The position is not important, the existence anywhere should give a 1.
Normally I am using references to text cells, but using actual text for testing and help here.

I am using the following formula: =IF(SEARCH("a","Maine")>0,1,"") which resuls in a 1,
but =IF(SEARCH("b","Maine")>0,1,"") results in #VALUE!

I have tried many variations of =IF(SEARCH("b","Maine")="VALUE!","",1) but cannot find a way to replace the #VALUE! with a zero or a blank.

2. ## Re: #VALUE! to be replaced (97)

Try the ISERROR function:

=IF(ISERROR(SEARCH("b","Maine")),"",1)

3. ## Re: #VALUE! to be replaced (97)

Thanks AGAIN Hans.
You just helped me out with Access problem last week. You seem to be everywhere!

Once again, the obvious is the correct solution.
And when you give the solution I want to hit myself on the head with a shovel because the solutions have been so simple once you pointed them out.
In this case, I could not accept the concept of an error, because the function returned the value it said it would. Aaargh!

Thank you again, your solution was perfect.

4. ## Re: #VALUE! to be replaced (97)

If you are searching for the presence of the contents of B1 in A1 and want to return one if found and zero if not, you can also use:
<code>=COUNTIF(A1,"*"&B1&"*")</code>
If B1 might be empty, you would need to check for that - something like: <code>=IF(B1<>"",COUNTIF(A1,"*"&B1&"*"),"")</code>
FWIW.

5. ## Re: #VALUE! to be replaced (97)

Thanks rory.

Another forgotten tool from the toolbox.
A good suggestion

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•