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

[Rutgers_Excels]

=IF(ISERROR(FIND("text",B1)),"",1)

[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


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

"anon90210" <googlegroups@keto.co.uk> wrote in message
news:369d3252.0501170445.686b5ab1@posting.google.com...
>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...?