Handling errors in formulas (how annoying are they!)
 

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

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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"anon90210" wrote in message
om...
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...?