SEARCH Function (LibreOffice Calc)
The SEARCH function in LibreOffice Calc returns the position of one text string within another, searching from left to right. It is case-insensitive and supports wildcards, making it ideal for flexible text parsing and pattern matching.
Tested: Examples checked in LibreOffice Calc 25.2.3.2. What this means
This guide provides syntax, wildcard rules, examples, edge cases, and best practices, making it one of the most complete SEARCH references available for LibreOffice Calc.
Native-tested visual example: Locate text without matching case
The phrase Inventory Report contains the word Report after the ten-character prefix Inventory .
Enter this formula in cell E5:
=SEARCH("report";A5)
Expected result: 11. SEARCH is case-insensitive, so report finds Report at position 11.
Real screenshot captured from LibreOffice Calc 25.2.3.2. The selected E5 cell shows the calculated result and the formula bar displays the entered formula.
Download the editable SEARCH example (.ods) — change the sample input cells and watch the calculation update.
Why it works: SEARCH is case-insensitive, so
reportfindsReportat position 11. Watch for: If no match is found, SEARCH returns an error; consider IFERROR when processing inconsistent data.
Verification: Example calculated in LibreOffice Calc 25.2.3.2. Screenshot is a genuine application capture, not a mockup.
What the SEARCH Function Does
- Returns the position of a substring within a string
- Is case-insensitive
- Supports wildcards (
?and*) - Returns an error if the substring is not found
- Searches from left to right
It is designed to be flexible, forgiving, and powerful for pattern-based parsing.
Syntax
SEARCH(find_text; within_text; [start])
Arguments
-
find_text:
The text or pattern to search for (supports wildcards). -
within_text:
The text to search inside. -
start: (optional)
The position to begin searching (1‑based).
Defaults to 1.
Wildcard Support
SEARCH supports two wildcards:
| Wildcard | Meaning |
|---|---|
? |
Matches any single character |
* |
Matches zero or more characters |
Examples:
"a?c"matches"abc""a*c"matches"ac","abc","axyzc"
Basic Examples
Case-insensitive search
=SEARCH("h"; "Hello")
Returns 1.
Search for a substring
=SEARCH("lo"; "Hello")
Returns 4.
Search with wildcard ?
=SEARCH("h?llo"; "Hello")
Returns 1.
Search with wildcard *
=SEARCH("he*o"; "Hello")
Returns 1.
Start searching from position 3
=SEARCH("l"; "Hello"; 3)
Returns 3.
Advanced Examples
Extract text before a delimiter
=LEFT(A1; SEARCH("-"; A1) - 1)
Extract text after a delimiter
=RIGHT(A1; LEN(A1) - SEARCH("-"; A1))
Extract middle text between two delimiters
=MID(A1; SEARCH("-"; A1) + 1; SEARCH("-"; A1; SEARCH("-"; A1) + 1) - SEARCH("-"; A1) - 1)
Find the Nth occurrence of a character
=SEARCH("☼"; SUBSTITUTE(A1; "-"; "☼"; 3))
Find the last occurrence of a character
=SEARCH("☼"; SUBSTITUTE(A1; "-"; "☼"; LEN(A1)-LEN(SUBSTITUTE(A1; "-"; ""))))
Case-insensitive validation
=IF(ISERROR(SEARCH("error"; A1)); "OK"; "Contains 'error'")
Extract file extension (case-insensitive)
=RIGHT(A1; LEN(A1) - SEARCH("."; A1))
Extract last name from “First Last”
=RIGHT(A1; LEN(A1) - SEARCH(" "; A1))
Extract first name
=LEFT(A1; SEARCH(" "; A1) - 1)
Edge Cases and Behavior Details
SEARCH is case-insensitive
=SEARCH("A"; "apple") → 1
=SEARCH("a"; "Apple") → 1
SEARCH supports wildcards; FIND does not
=SEARCH("a*e"; "Apple") → 1
=FIND("a*e"; "Apple") → #VALUE!
SEARCH returns #VALUE! if not found
=SEARCH("x"; "Hello") → #VALUE!
start < 1 → Err:502
=SEARCH("e"; "Hello"; 0)
start > text length → #VALUE!
=SEARCH("e"; "Hello"; 10)
SEARCH works with numbers (converted to text)
=SEARCH("2"; 2024) → 2
SEARCH respects Unicode characters
Positions count Unicode code points.
SEARCH returns first match only
No built‑in “find all” capability.
Common Errors and Fixes
#VALUE! — Pattern not found
Cause:
- Wrong delimiter
- Case mismatch (rare, since SEARCH is case-insensitive)
- Hidden characters
- Wildcards not matching as expected
Fix:
Use TRIM, CLEAN, or SUBSTITUTE.
Err:502 — Invalid argument
Cause:
- start < 1
- Non-numeric start
SEARCH returns unexpected position
Cause:
- Leading spaces
- Non-breaking spaces
- Unicode combining marks
Fix:
Normalize text with TRIM or CLEAN.
Best Practices
- Use SEARCH for flexible, case-insensitive parsing
- Use wildcards for pattern matching
- Combine with LEFT/RIGHT/MID for extraction
- Use SUBSTITUTE to target specific occurrences
- Use TRIM and CLEAN to normalize text before searching
- Use FIND when exact, case-sensitive matching is required
Related Patterns and Alternatives
- Use FIND for case-sensitive 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 SEARCH and its companion functions, you can build powerful, 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.
=SEARCH("Cat";"bobcat")
Expected result: 4.
Find the first position of text without requiring matching letter case.
If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.