FIND Function (LibreOffice Calc)
The FIND function in LibreOffice Calc returns the position of one text string within another, searching from left to right. It is case-sensitive and essential for delimiter-based parsing, text extraction, and data cleaning.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
What the FIND Function Does
- Returns the position of a substring within a string
- Searches from left to right
- Is caseāsensitive
- Does not support wildcards
- Returns an error if the substring is not found
It is designed to be precise, predictable, and ideal for structured text parsing.
Syntax
FIND(find_text; within_text; [start])
Arguments
-
find_text:
The text to search for. -
within_text:
The text to search inside. -
start: (optional)
The position to begin searching (1ābased).
Defaults to 1.
Basic Examples
Find the position of “e” in “Hello”
=FIND("e"; "Hello")
Returns 2.
Find “lo” in “Hello”
=FIND("lo"; "Hello")
Returns 4.
Case-sensitive search
=FIND("h"; "Hello")
Returns #VALUE! because “h” ā “H”.
Start searching from position 3
=FIND("l"; "Hello"; 3)
Returns 3.
Find a delimiter
=FIND("-"; A1)
Advanced Examples
Extract text before a delimiter
=LEFT(A1; FIND("-"; A1) - 1)
Extract text after a delimiter
=RIGHT(A1; LEN(A1) - FIND("-"; A1))
Extract middle text between two delimiters
=MID(A1; FIND("-"; A1) + 1; FIND("-"; A1; FIND("-"; A1) + 1) - FIND("-"; A1) - 1)
Find the position of the Nth occurrence of a character
=FIND("ā¼"; SUBSTITUTE(A1; "-"; "ā¼"; 3))
(Finds the 3rd dash.)
Find the last occurrence of a character
=FIND("ā¼"; SUBSTITUTE(A1; "-"; "ā¼"; LEN(A1)-LEN(SUBSTITUTE(A1; "-"; ""))))
Validate that a delimiter exists
=IF(ISERROR(FIND("-"; A1)); "Missing"; "OK")
Extract file extension
=RIGHT(A1; LEN(A1) - FIND("."; A1))
Extract first name from “First Last”
=LEFT(A1; FIND(" "; A1) - 1)
Extract last name
=RIGHT(A1; LEN(A1) - FIND(" "; A1))
Edge Cases and Behavior Details
FIND is case-sensitive
=FIND("a"; "Apple") ā #VALUE!
=FIND("A"; "Apple") ā 1
FIND does not support wildcards
Use SEARCH for wildcard support.
FIND returns #VALUE! if not found
=FIND("x"; "Hello") ā #VALUE!
start < 1 ā Err:502
=FIND("e"; "Hello"; 0)
start > text length ā #VALUE!
=FIND("e"; "Hello"; 10)
FIND works with numbers (converted to text)
=FIND("2"; 2024) ā 2
FIND respects Unicode characters
Positions count Unicode code points.
FIND returns first match only
No builtāin āfind allā capability.
Common Errors and Fixes
#VALUE! ā Substring not found
Cause:
- Case mismatch
- Wrong delimiter
- Hidden characters
Fix:
Use SEARCH for caseāinsensitive search.
Err:502 ā Invalid argument
Cause:
- start < 1
- Nonānumeric start
FIND returns unexpected position
Cause:
- Leading spaces
- Hidden characters
- Nonābreaking spaces
- Unicode combining marks
Fix:
Use TRIM, CLEAN, or SUBSTITUTE.
Best Practices
- Use FIND for precise, caseāsensitive parsing
- Combine with LEFT/RIGHT/MID for extraction
- Use SUBSTITUTE to target specific occurrences
- Use TRIM to normalize text before searching
- Use SEARCH when caseāinsensitivity is required
Related Patterns and Alternatives
- Use SEARCH for caseāinsensitive search
- Use LEFT, RIGHT, MID for extraction
- Use LEN to measure string length
- Use SUBSTITUTE to target specific occurrences
- Use TRIM and CLEAN to normalize text
By mastering FIND and its companion functions, you can build precise, flexible, and robust textāprocessing workflows in LibreOffice Calc.
Reproducible example (tested in LibreOffice Calc)
This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.
=FIND("cat";"bobcat")
Expected result: 4.
Find a case-sensitive substring in longer text.
If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.