|
SEARCH function #VALUE! result
Cell Formula for AZ5:
=IF(Q5="","N",IF(SEARCH("FD",Q5,21),"Y",IF(SEARCH ("FS",Q5,21),"Y","N"))) evaluates to #VALUE! for non-blank Q5 cell values that do not contain "FD", or that contain "FS". This is apparently by Microsoft design, but I still need to determine whether cells in column Q contain certain character sets in characters 2-13 (excluding the first character), and to record a Y (Yes) in column AZ in that event. The following are the results at present. It appears that the above formula will not work if the "FD" is not contained in the cell, causing the error message before the "IF FALSE" instruction evaluates. I guess what I'm looking for is a string-based "contains" function, but I can't seem to find one in Excel XP. My next thought is to construct a column for each character set to be tested, then a formula to select any "Y" result, but I doubt that will work with error messages in the cells. Any thoughts re keeping this simple would be appreciated. Cell Q5 Cell AZ5 (BLANK) N PFSTEST #VALUE! PFSFDTEST Y PFDTEST Y PFDFSTEST Y PFSTEST #VALUE! N N Mike B |
Your formula is incorrect, if you don't want search to return an error you
must condition it with isnumber or iserror, you also had a couple of parenthesis in the wrong place, try if this will work =IF(Q5="","N",IF(ISNUMBER(SEARCH("FD",Q5,2))1,"Y" ,IF(ISNUMBER(SEARCH("FS",Q5,2))1,"Y","N"))) Regards, Peo Sjoblom "Mike Boerne" wrote: Cell Formula for AZ5: =IF(Q5="","N",IF(SEARCH("FD",Q5,21),"Y",IF(SEARCH ("FS",Q5,21),"Y","N"))) evaluates to #VALUE! for non-blank Q5 cell values that do not contain "FD", or that contain "FS". This is apparently by Microsoft design, but I still need to determine whether cells in column Q contain certain character sets in characters 2-13 (excluding the first character), and to record a Y (Yes) in column AZ in that event. The following are the results at present. It appears that the above formula will not work if the "FD" is not contained in the cell, causing the error message before the "IF FALSE" instruction evaluates. I guess what I'm looking for is a string-based "contains" function, but I can't seem to find one in Excel XP. My next thought is to construct a column for each character set to be tested, then a formula to select any "Y" result, but I doubt that will work with error messages in the cells. Any thoughts re keeping this simple would be appreciated. Cell Q5 Cell AZ5 (BLANK) N PFSTEST #VALUE! PFSFDTEST Y PFDTEST Y PFDFSTEST Y PFSTEST #VALUE! N N Mike B |
Dear Peo,
Many, many, many thanks!!! It probably would have taken me another week to stumble upon isnumber or iserror, or to discern their proper use. Your suggestion appears to be the "silver bullet" I was looking for, although I can't imagine why it's not called "stringcontains." Just kidding about the "stringcontains," but my most sincere thanks for your kind assist. Now to figure out why it works! Thanks again. Regards, Mike Boerner -----Original Message----- Your formula is incorrect, if you don't want search to return an error you must condition it with isnumber or iserror, you also had a couple of parenthesis in the wrong place, try if this will work =IF(Q5="","N",IF(ISNUMBER(SEARCH("FD",Q5,2))1,"Y ",IF (ISNUMBER(SEARCH("FS",Q5,2))1,"Y","N"))) Regards, Peo Sjoblom "Mike Boerne" wrote: Cell Formula for AZ5: =IF(Q5="","N",IF(SEARCH("FD",Q5,21),"Y",IF(SEARCH ("FS",Q5,21),"Y","N"))) evaluates to #VALUE! for non-blank Q5 cell values that do not contain "FD", or that contain "FS". This is apparently by Microsoft design, but I still need to determine whether cells in column Q contain certain character sets in characters 2-13 (excluding the first character), and to record a Y (Yes) in column AZ in that event. The following are the results at present. It appears that the above formula will not work if the "FD" is not contained in the cell, causing the error message before the "IF FALSE" instruction evaluates. I guess what I'm looking for is a string-based "contains" function, but I can't seem to find one in Excel XP. My next thought is to construct a column for each character set to be tested, then a formula to select any "Y" result, but I doubt that will work with error messages in the cells. Any thoughts re keeping this simple would be appreciated. Cell Q5 Cell AZ5 (BLANK) N PFSTEST #VALUE! PFSFDTEST Y PFDTEST Y PFDFSTEST Y PFSTEST #VALUE! N N Mike B . |
Thanks for the thanks, you are one of the few that express any appreciation
However I noticed that after I posted my solution it wasn't correct, here's a revision that should work =IF(Q5="","N",IF(ISNUMBER(SEARCH("FD",Q5,2)),"Y",I F(ISNUMBER(SEARCH("FS",Q5,2)),"Y","N"))) I didn't notice the 1 which will make isnumber TRUE since the text expression of the error is greater than 1 it can be rewritten as =IF(OR(ISNUMBER(SEARCH({"FD";"FS"},Q5,2))),"Y","N" ) Regards, Peo Sjoblom "Mike Boerner" wrote: Dear Peo, Many, many, many thanks!!! It probably would have taken me another week to stumble upon isnumber or iserror, or to discern their proper use. Your suggestion appears to be the "silver bullet" I was looking for, although I can't imagine why it's not called "stringcontains." Just kidding about the "stringcontains," but my most sincere thanks for your kind assist. Now to figure out why it works! Thanks again. Regards, Mike Boerner -----Original Message----- Your formula is incorrect, if you don't want search to return an error you must condition it with isnumber or iserror, you also had a couple of parenthesis in the wrong place, try if this will work =IF(Q5="","N",IF(ISNUMBER(SEARCH("FD",Q5,2))1,"Y ",IF (ISNUMBER(SEARCH("FS",Q5,2))1,"Y","N"))) Regards, Peo Sjoblom "Mike Boerne" wrote: Cell Formula for AZ5: =IF(Q5="","N",IF(SEARCH("FD",Q5,21),"Y",IF(SEARCH ("FS",Q5,21),"Y","N"))) evaluates to #VALUE! for non-blank Q5 cell values that do not contain "FD", or that contain "FS". This is apparently by Microsoft design, but I still need to determine whether cells in column Q contain certain character sets in characters 2-13 (excluding the first character), and to record a Y (Yes) in column AZ in that event. The following are the results at present. It appears that the above formula will not work if the "FD" is not contained in the cell, causing the error message before the "IF FALSE" instruction evaluates. I guess what I'm looking for is a string-based "contains" function, but I can't seem to find one in Excel XP. My next thought is to construct a column for each character set to be tested, then a formula to select any "Y" result, but I doubt that will work with error messages in the cells. Any thoughts re keeping this simple would be appreciated. Cell Q5 Cell AZ5 (BLANK) N PFSTEST #VALUE! PFSFDTEST Y PFDTEST Y PFDFSTEST Y PFSTEST #VALUE! N N Mike B . |
| All times are GMT +1. The time now is 02:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com