At 11:45 19/05/2007 +0530, Pradeep Srinivas wrote:
List:
Using Calc, from OOo 2.2, on Linux
I would want to know if there is any way that the SEARCH (or FIND or
any other
equivalent function) would return ZERO if the searched item was not found.
For instance, if A1 contained "Server", then,
=SEARCH("Server"; A1) would return 1.
However, if A1 contained "JUNK", then,
=SEARCH("Server"; A1) returns "#VALUE!". I would rather that this
returns "0"
(or even "-1"), instead of a "#VALUE!".
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 )
--
Pradeep Srinivas
Bangalore, India
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.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
