Documentation/Calc Functions/SEARCH

Function name:
SEARCH

Category:
Text

Summary:
Locates the character position of the first occurrence of one text string within another.

SEARCH can be used with either wildcards or regular expressions. By default, the search is not case-sensitive, but a case-sensitive match can be carried out when using a regular expression.

Syntax:
SEARCH(FindText; Text[; Position])

Returns:
Returns a positive integer which is the position of the first character of the first occurrence of the string to be found, within the string to be searched. The value returned cannot be greater than the length of the string to be searched.

Arguments:
FindText is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the string to be found.

Text is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that is the string to be searched.

Position is a positive integer, or a reference to a cell containing a positive integer, that is the character position from which the search starts. If Position is omitted, SEARCH uses the value 1.


 * If Position is a non-integer value, then SEARCH truncates it to an integer value.
 * If Position is less than 1, then SEARCH reports an invalid argument error (Err:502).
 * After truncation, if Position is greater than the length of the string to be searched, then SEARCH reports a #VALUE! error.
 * If no match is found, then SEARCH reports a #VALUE! error. This is an error condition, which must be handled if used as an argument to another function.

Details specific to SEARCH function
In (or  on macOS), the setting for Search criteria = and <> must apply to whole cells has no effect on the behavior of SEARCH.

Suggestions for handling the #VALUE! error that is returned when no match is found include:


 * returns "ERR: Missing Substring" and does not propagate the error from the SEARCH function.


 * returns "ERR: Missing Substring" and does not propagate the error from the SEARCH function.

Examples:
Unless explicitly stated, the following example formulas should work as described whether wildcards, regular expressions, or neither are enabled.

Related LibreOffice functions:
EXACT

FIND

SEARCHB

ODF standard:
Section 6.20.20, part 2

Equivalent Excel functions:
SEARCH