SEARCH and FIND function - VALUE ERROR !

Hi,

I have a #VALUE error in FIND or SEARCH function.

What I want is to display "0" in a formula cell, If cell D6 cointains text <empty>. If It doesn't contain this text, I want cell to display value from C9.

I know FIND and SEARCH function return #VALUE error If text is not TRUE, but can I overcome this ? Or maybe solve my problem with other function ??

These are formulas I tried so far (none working):

=IF(FIND("<empty>";D6);0;C9)

=IF(FIND("<empty>";D6);0;IF(ISNUMBER(SEARCH("-";C9));C9;0))

=IF(ISNUMBER(SEARCH("-";C9));IF(FIND("<empty>";D6);0);C9)

See attached sample !

Thanks for help !

Try

=IF(ISERROR(SEARCH("<empty>";D6));C9;0)

Originally Posted by TheCman81

unless I'm missing something

I think the string in D6 contains more than just <empty>, like
"there is more text besides <empty> in the cell"

Here's another method..

=IF(COUNTIF(D6;"*<empty>*");0;C9)

Thanks for all responses,

All formulas you provided works except Jacc's one where only True and False needs to be reversed) !!

thanks for help, I will use first one :

=IF(ISERROR(SEARCH("<empty>";D6));C9;0)

You're welcome.