Handling errors in formulas (how annoying are they!)

[anon90210]

I want to write a formula that returns a 1 if the given text is found
in another cell, or "" if it is not. This seemingly simple formula is
driving me nutty because of excels wacky error codes.

eg.

[a1] =if(find("text",b1),1,"")
[a2] =if(find("text",b2),1,"")
[b1]="here is some text"
[b2]="here is some words"

This returns

[a1] 1
[a2] #VALUE!

how do I get it to work properly...?

 

[Bernard Liengme]

The trouble is FIND returns an error value when the text is not found.
You turn this to your advantage and use =IF(ISERROR(FIND("text",A1)),"",1)

best wishes