Contact
12 programs
Explore the site

TEXTBEFORE Function (LibreOffice Calc)

Text Intermediate LibreOffice Calc LibreOffice 25.8

The TEXTBEFORE function in LibreOffice Calc extracts text that appears before a specified delimiter. It supports instance selection, case sensitivity options, and default return values, making it a modern and powerful text-extraction tool.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: LibreOffice 25.8.
  • Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.

Version compatibility: TEXTBEFORE became available in LibreOffice Calc 25.8. It is not available in Calc 25.2 or older releases. See the official LibreOffice 25.8 release notes and function reference.

The **TEXTBEFORE** function extracts text that appears before a specified delimiter in LibreOffice Calc. It supports selecting which occurrence to use, optional case sensitivity, and default return values. TEXTBEFORE is a modern, powerful replacement for complex LEFT/FIND formulas traditionally used for delimiter-based extraction.

What the TEXTBEFORE Function Does

  • Extracts text before a delimiter
  • Supports selecting the Nth occurrence
  • Supports case-sensitive or insensitive matching
  • Allows a default value if the delimiter is missing
  • Works with text, numbers (converted to text), and formulas

It is designed to be clean, modern, and extremely flexible.

Syntax

TEXTBEFORE(text; delimiter; [instance]; [match_mode]; [match_end]; [if_not_found])

Arguments

  • text:
    The text to extract from.

  • delimiter:
    The text or pattern to search for.

  • instance: (optional)
    Which occurrence to use (default = 1).

    • 1 → first occurrence
    • 2 → second occurrence
    • -1 → last occurrence
    • -2 → second-last occurrence

  • match_mode: (optional)

    • 0 → case-sensitive (default)
    • 1 → case-insensitive

  • match_end: (optional)

    • 0 → delimiter must be fully matched (default)
    • 1 → match delimiter at end of text

  • if_not_found: (optional)
    Value to return if delimiter is missing (default = #N/A).

Basic Examples

Extract text before the first hyphen

=TEXTBEFORE("A-B-C"; "-")

Returns "A".

Extract text before the second hyphen

=TEXTBEFORE("A-B-C"; "-"; 2)

Returns "A-B".

Extract text before the last hyphen

=TEXTBEFORE("A-B-C"; "-"; -1)

Returns "A-B".

Case-insensitive extraction

=TEXTBEFORE("HelloWORLD"; "world"; 1; 1)

Returns "Hello".

Provide a default value if delimiter missing

=TEXTBEFORE("ABC"; "-"; 1; 0; 0; "Not found")

Returns "Not found".

Advanced Examples

Extract filename without extension

=TEXTBEFORE(A1; "."; -1)

Extract username from email

=TEXTBEFORE(A1; "@")

Extract first word

=TEXTBEFORE(A1; " ")

Extract everything before the second space

=TEXTBEFORE(A1; " "; 2)

Extract before a long delimiter

=TEXTBEFORE(A1; " - ")

Extract before a dynamic delimiter

=TEXTBEFORE(A1; B1)

Extract before delimiter only if present

=TEXTBEFORE(A1; "-"; 1; 0; 0; A1)

Extract before the last slash in a file path

=TEXTBEFORE(A1; "/"; -1)

Extract before a substring ignoring case

=TEXTBEFORE(A1; B1; 1; 1)

Extract before a delimiter that appears many times

=TEXTBEFORE(A1; "|"; 5)

Extract before a delimiter at end of string

=TEXTBEFORE("ABC-"; "-"; 1; 0; 1)

Returns "ABC".

Edge Cases and Behavior Details

Missing delimiter → #N/A unless if_not_found provided

=TEXTBEFORE("ABC"; "-") → #N/A

instance < 0 counts from the end

=TEXTBEFORE("A-B-C-D"; "-"; -2) → "A-B"

instance too large → #N/A

=TEXTBEFORE("A-B"; "-"; 5) → #N/A

match_mode controls case sensitivity

=TEXTBEFORE("ABCdef"; "DEF"; 1; 1) → "ABC"

match_end allows matching delimiter at end

=TEXTBEFORE("ABC-"; "-"; 1; 0; 1) → "ABC"

text is a number → converted to text

=TEXTBEFORE(2024; "2") → ""

delimiter is empty → Err:502

text is empty → returns empty string

TEXTBEFORE of an error propagates the error

=TEXTBEFORE(#N/A; "-") → #N/A

Common Errors and Fixes

#N/A — Delimiter not found

Fix:
Provide an if_not_found value.

Err:502 — Invalid argument

Occurs when:

  • delimiter is empty
  • instance is non-numeric
  • match_mode invalid

Unexpected extraction

Cause:

  • Case sensitivity mismatch
  • Wrong instance number
  • Hidden characters in delimiter

Fix:
Use CLEAN, TRIM, or SUBSTITUTE.

Best Practices

  • Use TEXTBEFORE instead of LEFT/FIND chains
  • Use negative instance values to extract from the end
  • Use match_mode for case-insensitive extraction
  • Always provide if_not_found for robust formulas
  • Combine with TEXTAFTER for full delimiter-based parsing
  • Use TRIM and CLEAN before extraction for reliability
TEXTBEFORE is your modern pre-delimiter extractor — perfect for clean, readable, and robust parsing without legacy formula complexity.
  • Use TEXTAFTER to extract text after a delimiter
  • Use SEARCH or FIND for positional extraction
  • Use LEFT, RIGHT, MID for manual parsing
  • Use SPLIT (future Calc versions) for array-based splitting
  • Use SUBSTITUTE for targeted cleanup

By mastering TEXTBEFORE and its companion functions, you can build powerful, modern, and highly readable text‑processing workflows in LibreOffice Calc.