using "find" or "search" to find commas
 

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

using "find" or "search" to find commas
 

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

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

using "find" or "search" to find commas
 

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

using "find" or "search" to find commas
 

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

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