TEXTBEFORE Function (LibreOffice Calc)

• 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.

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).

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".

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".

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

• 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

• 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.