Collabora Office TEXTBEFORE: Extract a prefix with explicit occurrence rules
Extract prefixes in the new Linux desktop build while preserving spaces, selecting negative instances and distinguishing fallback text from errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =TEXTBEFORE("north/east/report";"/";-1) independently gives north/east. The default first occurrence instead gives north. The selected delimiter is excluded, while earlier delimiters and whitespace remain part of the prefix.
Syntax and defaults
=TEXTBEFORE(Text [; Delimiter [; Instance_Number [; Match_Mode [; Match_End [; If_Not_Found]]]]])
The 26.04 help documents instance 1, case-sensitive Match Mode 0, Match End 0, and not-available #N/A when no occurrence is found and no fallback is supplied. Negative instances count from the last recorded delimiter. Supply the intervening separators when setting a later optional argument.
Use nonempty source text and an explicit literal delimiter for ordinary extraction. Multiple delimiters can be supplied in a range or array, but they are not regular-expression alternatives or wildcard patterns. The inspected search chooses the earliest next occurrence and advances past that matched delimiter. It does not count overlapping matches separately.
Match Mode 1 enables case-insensitive search. Match End 1 adds the text’s end as an additional delimiter boundary for TEXTBEFORE. That option changes the occurrence list, especially for negative instances; it is not simply a cosmetic error-handling option.
Worked example: extract a group and parent path
Enter north/east/report as text in A1.
| Formula | Independently expected text |
|---|---|
=TEXTBEFORE(A1;"/") |
north |
=TEXTBEFORE(A1;"/";2) |
north/east |
=TEXTBEFORE(A1;"/";-1) |
north/east |
The first slash follows north. The second follows east, so everything before it includes the first slash. TEXTBEFORE does not understand directory semantics or normalize repeated separators; this is plain substring extraction using the exact text supplied.
If the labels later gain another slash-delimited component, positive instance 2 still means the second delimiter from the beginning, while instance −1 follows the final delimiter. Choose the rule that matches the structure you actually intend.
Advanced use: fallback and end-boundary policy
For optional separators, =TEXTBEFORE("abc";"/";1;0;0;"missing") predicts the text missing. An explicit fallback makes a failed parse visible. A numeric fallback 0 is converted by the string helper into text “0”, so its type differs from a numerical calculation result.
If the intended policy is “return the whole input when there is no delimiter,” Match End = 1 can provide a synthetic final boundary in this pinned implementation. With source a/b, the recorded prefix endpoints are the slash position and the text length:
| Formula | Source-derived text |
|---|---|
=TEXTBEFORE("a/b";"/";1;0;1) |
a |
=TEXTBEFORE("a/b";"/";2;0;1) |
a/b |
=TEXTBEFORE("a/b";"/";-1;0;1) |
a/b |
Consequently, adding Match End = 1 to an existing negative-instance formula can change the result from the parent prefix to the entire string. TEXTAFTER uses a different synthetic-boundary placement in this build; do not transfer these examples to it unchanged.
Spaces and case are separate decisions
=TEXTBEFORE("To be or not to be";"be";2) predicts “To be or not to “, with one trailing space. The official example’s prose omits that boundary space, but the pinned copy operation preserves it. A later comparison with a trimmed label can therefore fail even when the visible words look identical.
=TEXTBEFORE("a/B/c";"b";1;1) predicts a/. The search ignores ASCII letter case for the delimiter, while the returned prefix remains unchanged. This does not establish every language’s case equivalence; the source uses the engine character-class service.
Errors and unusual input
Instance 0 follows internal NotAvailable immediately, before a fallback can be selected. A too-large occurrence number uses If_Not_Found if supplied, otherwise the same not-available result. Empty source text follows IllegalParameter. These are inspected evaluator outcomes; localized UI error strings were not measured in an application.
The delimiter argument is optional in help and the source. With no actual delimiter and Match End = 0, the occurrence list is empty, so fallback/error applies. Empty delimiter strings are skipped rather than defining every character boundary. Avoid relying on implicit number-to-text formatting for delimiters or source values when locale would affect the result.
The precise behavior comes from ScTextBeforeOrAfter(true), including the separate handling of real delimiters, the synthetic end and substring copying.
Product scope, compatibility and evidence
This guide covers the new Collabora Office desktop for Linux, using the Flathub source tag coda-26.04.4.3-1 and commit 81339d176148d0d5c81ff05d82540f58f8aaa117. The official desktop release notes and Linux package manifest identify that product/build. The package manifest is a dated observation from October 18, 2026, rather than an installed-binary check.
The 26.04 function help and exact evaluator source were inspected on October 18, 2026. Expected examples were independently calculated; no Collabora application was run. Expected values and arrays describe source-derived results, not verified keyboard entry or automatic spill behavior. Classic, Online/CODE, other operating systems and file-format round trips require their own checks.
The help identifies this function as outside OpenDocument 1.3 and uses the extension name COM.MICROSOFT.TEXTBEFORE. That does not establish lossless export to every spreadsheet format. The first Collabora release introducing it is not established by these sources. It is documented here as available, with no cited retirement or deprecation notice; an undocumented introduction date is not a deprecation claim.