using "find" or "search" to find commas

[Shakespeare]

Here's the formula:

=IF(SEARCH(",",F121)>0,"y","n")

I'm looking for commas, the above formula returns a "y" when it finds a
comma, however it doesn't return the "n" when it doesn't... I get a
#value! error.

What have I done wrong?

 

[Biff]

Hi!

If there is no comma the SEARCH function returns the #VALUE! error which is
in turn passed to IF.

Try this:

=IF(ISNUMBER(FIND(",",F121)),"y","n")

Biff

"Shakespeare" <[email protected]>
wrote in message
news:[email protected]...

 

[Shakespeare]

Biff, I thank you as your formula worked perfectly... but I'm trying to
understand why. I thought that when using an IF statement, the second
value represents the "else". My logic says that if I had written the
statement and omitted the second value (the "n" in this scenario) it
would have given me the error.

Can you give me some direction on why your formula works so that I can
do better next time?

thanks again,
JoAnne

 

[Biff]

Hi!

If there is no comma Search/Find will return #VALUE!. Since #VALUE! does not
evaluate to either TRUE or FALSE the IF formula returns #VALUE!.

Isnumber is a good way to prevent that. If there is no comma the #VALUE!
error is passed to Isnumber:

ISNUMBER(#VALUE!)

That evaluates to FALSE and allows the IF formula to return the
value_if_false argument.

If you omit the value_if_false argument it'll default to return a boolean
FALSE.

Biff

"Shakespeare" <[email protected]>
wrote in message
news:[email protected]...