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]...
>
> 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?
>
>
> --
> Shakespeare
> ------------------------------------------------------------------------
> Shakespeare's Profile:
> http://www.excelforum.com/member.php...o&userid=21552
> View this thread: http://www.excelforum.com/showthread...hreadid=530809
>

[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]...
>
> 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
>
>
> --
> Shakespeare
> ------------------------------------------------------------------------
> Shakespeare's Profile:
> http://www.excelforum.com/member.php...o&userid=21552
> View this thread: http://www.excelforum.com/showthread...hreadid=530809
>