Documentation/Calc Functions/SEARCH
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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.
Additional details:
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:
=IF(ISERROR(SEARCH("xyz"; "abcdef"; 1)); "ERR: Missing Substring"; "Substring Present")returns "ERR: Missing Substring" and does not propagate the error from the SEARCH function.=IF(ISNUMBER(SEARCH("xyz"; "abcdef"; 1)); "Substring Present"; "ERR: Missing Substring")returns "ERR: Missing Substring" and does not propagate the error from the SEARCH function.
General information about Calc's regular expressions
 |
Note:
|
- A regular expression is a string of characters defining a pattern of text that is to be matched. More detailed, general background information can be found on Wikipedia’s Regular expression page.
- Regular expressions are widely used in many domains and there are multiple regular expression processors available. Calc utilises the open source Regular Expressions package from the International Components for Unicode (ICU). See their Regular Expressions documentation for further details, including a full definition of the syntax for ICU Regular Expressions.
- In addition, the LibreOffice Help system provides a high-level list of regular expressions.
- Calc’s regular expression engine supports numbered capture groups, which allow sub-ranges within a match to be identified and used within replacement text. Parentheses are used to group components of a regular expression together and create a numbered capture group. To insert a capture group into a replacement text, use the "$n" form, where n is the number of the capture group.
General information about Calc's wildcards
Wildcards are special characters that can be used in search strings passed as arguments to some Calc functions; they can also be used to define search criteria in the Find & Replace dialog. The use of wildcards enables the definition of more advanced search parameters with a single search string.
Calc supports either wildcards or regular expressions as arguments, depending on the current application settings. By default, wildcards are supported instead of regular expressions.
To make sure wildcards are supported, go to ▸ ▸ ▸ and check whether the option Enable wildcards in formulas is selected. Note that you can use this dialog to switch to regular expressions by choosing Enable regular expressions in formulas or choose to support neither wildcards nor regular expressions.
The following table identifies the wildcards that Calc supports.
| Wildcard | Description |
|---|---|
| ? (question mark) | Matches any single character. For example, the search string "b?g" matches "bag" and "beg" but will not match "boog" or "mug". Note that it will not match "bg" either, since "?" must match exactly one character. The "?" wildcard does not correspond to a zero-character match. |
| * (asterisk) | Matches any sequence of characters, including an empty string. For example, the search string "*cast" will match "cast", "forecast", and “outcast”, but will not match "forecaster" using default Calc settings. If the option Search criteria = and <> must apply to whole cells is disabled in ▸ ▸ ▸ , then "forecaster" will be a match using the "*cast" search string.
|
| ~ (tilde) | Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character. For example, the search string "why~?" matches "why?" but will not match "whys" nor "why~s". |
Wildcard comparisons are not case sensitive, hence "A?" will match both "A1" and "a1".
These wildcards are supported in both Calc and Microsoft Excel. Therefore, if interoperability between the two applications is needed, choose to work with wildcards instead of regular expressions. Conversely, if interoperability is not necessary, consider using regular expressions for more powerful search capabilities.
Examples:
Unless explicitly stated, the following example formulas should work as described whether wildcards, regular expressions, or neither are enabled.
| Formula | Description | Returns |
|---|---|---|
| =SEARCH("fifty"; "Fifty-fifty") | Here the function returns 1 because the match is case-insensitive. | 1 |
| =SEARCH(76; 998,877,667,654) | Here the function returns 6 because by default matching starts at the first character of the string to be searched. | 6 |
| =SEARCH(76; 998,877,667,654; 7) | Here the function returns 9 because matching starts at the seventh character of the string to be searched. | 9 |
| =SEARCH(D1; D2; D3) where cells D1 and D2 contain the strings "Rook" and "Knight", while cell D3 contains the value 1. | Here the function returns the #VALUE! error because there is no match. | #VALUE! |
| =SEARCH("b??g"; "big bang theory") | For this example, make sure that wildcards are enabled. Here the function returns 5, matching "bang" but not "big". | 5 |
| =SEARCH("w*"; "What is the weather forecast?") | For this example, make sure that wildcards are enabled. Here the function returns 1, demonstrating that the match with wildcards is case-insensitive. | 1 |
| =SEARCH("t.n"; "often") | For this example, make sure that regular expressions are enabled. Here the function returns 3, matching "ten". | 3 |
| =SEARCH("(?-i)fifty"; "Fifty-fifty") | For this example, make sure that regular expressions are enabled. Here the function returns 7 because the "(?-i)" option within the regular expression has changed to a case-sensitive match. Contrast this with the first example in this table. | 7 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
SEARCH