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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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.