On Saturday 19 May 2007, Brian Barker wrote (regarding Re: [users] "SEARCH"
function - return value - help requested):
> At 11:45 19/05/2007 +0530, Pradeep Srinivas wrote:
<<SNIPPED>>
> >Am I doing something fundamentally wrong ? If not, is there any
> >function that
> >would
> >a. return 0 if a search item was not found
> >or
> >b. take the #VALUE and convert it to something that a logical function
> > (like IF() ) can use ?
> >or
> >c. is there a function that behaves like the "regular expression match"
> >operator in awk (the "~" operator, as in "$1 ~ Server )
<<SNIPPED>>
On Saturday 19 May 2007, Brian Barker answered:
> The answer to your question (b) is yes: ISNUMBER is one such. One
> way to get zero when there is no match would be to use:
>
> =IF(ISNUMBER(SEARCH("Server";A1));SEARCH("Server";A1);0)
>
> It's messy, but it works. You could simplify it by putting the
> search text in a cell, or even the result of SEARCH in another
> cell. Perhaps there are neater solutions?
>
> As for (c), the SEARCH function (but not the FIND function) supports
> regular expressions; in other words, you can include them in the
> first parameter. I don't know whether that answers your question.
>
> I trust this helps.
List:
Thanks to Brian (b.m.barker at btinternet dot com) and based on his (Brian's)
inputs of "ISNUMBER()" function, I rooted around for a while and came up with
the "ISERROR()" and the "ISERR()" functions that do the trick as well (as
Brian's solution).
However, I cannot get the "~" operator to work (as in AWK, which, in itself,
is not a major problem - just a nice to have).
Any other solutions are also welcome - especially any function that would
return a Zero for no-match, and position-found for a match.....or is this a
wish list ?
Many thanks to Brian, and all of you out there.
--
Pradeep Srinivas
Bangalore, India
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
