SEARCH function
From Apache OpenOffice Wiki
< Documentation | How Tos
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
SEARCH
Returns the position of a string of text within another string.
Syntax:
SEARCH(findtext; texttosearch; startposition)
- returns the character position of the first occurrence of findtext within texttosearch.
- startposition (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 #VALUE! error.
- In Tools → Options → OpenOffice Calc → Calculate the setting for Search criteria = and <>must apply to whole cells has no effect.
Example:
SEARCH("yo"; "Yoyo")
- returns 1. The search is case-insensitive.
SEARCH("cho"; "choochoo"; 2)
- returns 5.
SEARCH("t.n"; "often")
- returns 3, if regular expressions are enabled. The "." stands for any single character in a regular expression, so "t.n" matches "ten".
SEARCH("xyz","abcdef",1)
- returns #VALUE!.
- NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
- IF( SEARCH("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )
- returns #VALUE! which is not very useful, therefore we could use either ISERROR() or ISNUMBER() for example:
- IF( ISERROR( SEARCH("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" ).
- returns "ERR: Missing Substring" (... allowing the IF() to function, and not propagating the error from the SEARCH() function.
- IF( ISNUMBER( SEARCH("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" ).
- returns "ERR: Missing Substring" (... 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 indicative of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.
See Also
