Apache OpenOffice Wiki - User contributions [en]

Talk:Documentation/How Tos/Calc: SEARCH function

2011-02-18T02:37:59Z

Practicalcode: Case Rationale and theoretical discussion of result type/value.

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:
[http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=29701&start=0&sid=fe3fe5e9b27e6666f4bc7fac5973549d 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 [http://user.services.openoffice.org/en/forum/index.php 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

Talk:Documentation/How Tos/Calc: FIND function

2011-02-18T02:19:07Z

Practicalcode: Case Rationale and theoretical discussion of result type/value.

Documentation/How Tos/Calc: SEARCH function

2011-02-18T02:04:08Z

Practicalcode: Added example to cover case where substring is not present.

__NOTOC__

== SEARCH ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''SEARCH(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is '''not''' case-sensitive.

: The search will use regular expressions, if they are enabled ('''Tools - Options - OpenOffice.org Calc - Calculate''').

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

: In '''Tools - Options - OpenOffice.org Calc - Calculate''' the setting for <i>Search criteria = and <>must apply to whole cells</i> has no effect.

=== Example: ===
<tt>'''SEARCH("yo"; "Yoyo")'''</tt>
: returns <tt>'''1'''</tt>. The search is case-insensitive.

<tt>'''SEARCH("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''SEARCH("t.n"; "often")'''</tt>
: returns <tt>'''3'''</tt>, if regular expressions are enabled. The "." stands for any single character in a regular expression, so <tt>'''"t.n"'''</tt> matches <tt>'''"ten"'''</tt>.

<tt>'''SEARCH("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( SEARCH("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( SEARCH("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the SEARCH() function.
:: <tt>'''IF( ISNUMBER( SEARCH("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the SEARCH() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicitive of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.
{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: FIND function|FIND]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]

Documentation/How Tos/Calc: FIND function

2011-02-18T01:59:15Z

Practicalcode: Added example to cover case where substring is not present.

__NOTOC__

== FIND ==
Returns the position of a string of text within another string.

=== Syntax: ===
<tt>'''FIND(findtext; texttosearch; startposition)'''</tt>
: returns the character position of the first occurrence of <tt>'''findtext'''</tt> within <tt>'''texttosearch'''</tt>.

: <tt>'''startposition'''</tt> (optional) is the position from which the search starts.

: The search is case-sensitive.

: A failed search gives the <tt>'''#VALUE!'''</tt> error.

=== Example: ===
<tt>'''FIND("yo"; "Yoyo")'''</tt>
: returns <tt>'''3'''</tt>.

<tt>'''FIND("cho"; "choochoo"; 2)'''</tt>
: returns <tt>'''5'''</tt>.

<tt>'''FIND("xyz","abcdef",1)'''</tt>
: returns <tt>'''#VALUE!'''</tt>.
: NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
: <tt>'''IF( FIND("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )'''</tt>
:: returns <tt>'''#VALUE!'''</tt> which is not very useful, therefore we could use either <tt>'''ISERROR()'''</tt> or <tt>'''ISNUMBER()'''</tt> for example:
:: <tt>'''IF( ISERROR( FIND("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: <tt>'''IF( ISNUMBER( FIND("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" )'''</tt>.
::: returns <tt>'''"ERR: Missing Substring"'''</tt> (... allowing the IF() to function, and not propagating the error from the FIND() function.
:: NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicitive of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

{{Documentation/SeeAlso|
* [[Documentation/How_Tos/Calc: REPLACE function|REPLACE]]
* [[Documentation/How_Tos/Calc: SEARCH function|SEARCH]]
* [[Documentation/How_Tos/Calc: SUBSTITUTE function|SUBSTITUTE]]
* [[Documentation/How_Tos/Calc: EXACT function|EXACT]]

* [[Documentation/How_Tos/Calc: Text functions|Text functions]]

* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
[[Category: Documentation/Reference/Calc/Text functions]]