SEARCH function #VALUE! result

[Mike Boerne]

Cell Formula for AZ5:

=IF(Q5="","N",IF(SEARCH("FD",Q5,2>1),"Y",IF(SEARCH("FS",Q5,2>1),"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

[Peo Sjoblom]

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,2>1),"Y",IF(SEARCH("FS",Q5,2>1),"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

[Mike Boerner]

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,2>1),"Y",IF(SEARCH
("FS",Q5,2>1),"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
>.
>

[Peo Sjoblom]

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",IF(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,2>1),"Y",IF(SEARCH
> ("FS",Q5,2>1),"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
> >.
> >
>