Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace | Excel Worksheet Functions | |||
Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
Edit + Find | Excel Discussion (Misc queries) | |||
can't update links...can't find links | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions |