Browse

FIND Function (LibreOffice Calc)

Text Intermediate LibreOffice Calc LibreOffice 3.0

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.

The **FIND** function returns the position of one text string within another in LibreOffice Calc. It is case‑sensitive and does not support wildcards, making it ideal for precise, structured text parsing. FIND pairs naturally with `LEFT`, `RIGHT`, `MID`, and `LEN`.

This guide provides syntax, examples, edge cases, and best practices, making it one of the most complete FIND references available for LibreOffice Calc.

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.

=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
FIND is the backbone of delimiter‑based parsing — combine it with LEN, LEFT, RIGHT, and MID for powerful text‑processing workflows.
  • 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.