Talk:Documentation/How Tos/Calc: SEARCH function

I added the 'not found' case because I think it is essential to understanding the FIND() && SEARCH() functions. I elaborate further in the forum that caused me to explore the answer for my own purposes: How to avoid #VALUE!

I would like to re-iterate however, that I believe the definition of the function should be redefined. If I were defining the function, my vote would be for it to return 0 and not #VALUE!. I think #VALUE! is misused in this instance. All of the arguments were legal, the substring simply did not exist.

(< Though, I do understand the difficulty of doing so at this point with the potential of 'breaking' existing spreadsheets that use the ISNUMBER() work around... so perhaps in hindsight I should have abdicated the use of ISERROR() even with the reverse logic?... Perhaps we need an ISVALID() function which is defined as NOT ISERROR() >)

If someone knows of the decision process / rationale behind returning #VALUE! for the FIND() && SEARCH() functions, pls feel free to enlighten me with an e-mail.

--PracticalCode

These examples are a little facile for me. I'm trying to figure out whether it's possible to SEARCH( "text_to_find", MyOtherSheet!NamedRange ) -- that is, search a range of cells for the needle, and if so, how to refer to the named range. (Question from a newbie spreadsheet user / engineering type.)

-- LNBEL

Probably better asked in the forums Open Office Community Forum esp in hindsight considering noone has responded since 17 February 2010 according to the history.

That aside, I would suppose I'd ask what the purpose of finding the location of the '1st' instance of a substring in a range would be?.. If you simply needed to know if it existed or not, then COUNTIF() would work; SUMIF() for a conditional totaling...

--PracticalCode