Documentation/Calc Functions/FIND
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
FIND
Category:
Text
Summary:
Uses a case-sensitive match to find the position of one text string within another text string.
Syntax:
FIND(Find Text; 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:
Find 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 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, FIND uses the value 1.
- If Position is a non-integer value, FIND truncates it to an integer value.
- After truncation, if Position is less than 1 or greater than the length of the string to be searched, then FIND reports a #VALUE! error.
- If no match is found, then FIND reports a #VALUE! error. This is an error condition, which must be handled if used as an argument to another function.
Additional details:
FIND does not support wildcards or regular expressions. Use the SEARCH function if you require searches with wildcards or regular expressions, or if you need searches that are not case sensitive.
In ▸ ▸ ▸ (or ▸ ▸ ▸ on macOS), the setting for Search criteria = and <> must apply to whole cells has no effect on the behavior of FIND.
Suggestions for handling the #VALUE! error that is returned when no match is found include:
=IF(ISERROR(FIND("xyz"; "abcdef"; 1)); "ERR: Missing Substring"; "Substring Present")returns "ERR: Missing Substring" and does not propagate the error from the FIND function.=IF(ISNUMBER(FIND("xyz"; "abcdef"; 1)); "Substring Present"; "ERR: Missing Substring")returns "ERR: Missing Substring" and does not propagate the error from the FIND function.
Examples:
| Formula | Description | Returns |
|---|---|---|
| =FIND("fifty"; "Fifty-fifty") | Here the function returns 7 because the match is case-sensitive. | 7 |
| =FIND(76; 998877667654) | Here the function returns 6 because by default matching starts at the first character of the string to be searched. | 6 |
| =FIND(76; 998877667654; 7) | Here the function returns 9 because matching starts at the seventh character of the string to be searched. | 9 |
| =FIND(D1; D2; D3) where cell D1, D2, and D3 contain "Rook", "Knight" and the number 1 respectively. | Here the function returns the #VALUE! error because there is no match. | #VALUE! |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
FIND