FILTER and TEXTAFTER change error identity in LibreOffice
Modern array and text functions change not just their values but their error identity
across engines. A FILTER with no matching rows and no if_empty argument is
documented by Microsoft as #CALC!; LibreOffice Calc returns #N/A instead.
And =TEXTAFTER("abc","-","none") — which supplies an if_not_found fallback
precisely so it will not error — returns "none" per Microsoft but #VALUE! in
LibreOffice. Both break the error-handling patterns built around them.
The surprise
The same no-match condition surfaces as a different error: #CALC! in Excel,
#N/A in LibreOffice. And a fallback argument that is supposed to prevent an error
altogether is ignored by LibreOffice's TEXTAFTER/TEXTBEFORE, which return
#VALUE! anyway. Any ISNA or IFERROR guard written for one
engine can silently stop matching in the other.
Executed results
The FILTER rows use A1:A3 = 1, 2, 3 and B1:B3 = 1, 2, 3.
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =FILTER(A1:A3,B1:B3>100) | #CALC! | Not yet executed | #N/A |
| =FILTER(A1:A3,B1:B3>100,"none") | none | Not yet executed | none |
| =TEXTAFTER("abc","-","none") | none | Not yet executed | #VALUE! |
| =TEXTBEFORE("abc","-","none") | none | Not yet executed | #VALUE! |
| =TEXTBEFORE("abc","-") | #N/A | Not yet executed | #N/A |
The Excel column is the documented-expected result from our test corpus; we did not run Excel.
The LibreOffice column is what our harness computed in LibreOffice Calc 25.8.7.3. Note the pattern:
supplying the fallback fixes FILTER (both return "none") but not
TEXTAFTER/TEXTBEFORE, and a plain no-match TEXTBEFORE without a
fallback agrees on #N/A in both engines.
These functions are new to LibreOffice
Version spread matters a lot here. FILTER did not exist in 24.2.0.3 — every
FILTER case returned #NAME? in that build — and only started evaluating from
24.8.7.2, where the empty-result case has returned #N/A ever since (24.8.7.2, 25.2.0.3,
25.8.7.3). TEXTAFTER and TEXTBEFORE are newer still: they returned
#NAME? in 24.2.0.3, 24.8.7.2 and 25.2.0.3, and only landed in 25.8.7.3. So on
any LibreOffice older than 25.8 these text functions do not exist at all, and even in 25.8.7.3 the
if_not_found argument returns #VALUE! rather than the fallback text.
Why it happens
#CALC! is an Excel-specific error for an empty array result; LibreOffice has no such
code and reports an empty FILTER as #N/A. For the text functions,
LibreOffice's freshly added TEXTAFTER/TEXTBEFORE do not yet honour the
if_not_found fallback when the delimiter is absent, so a call designed never to error
does exactly that. In both cases the engine is answering a slightly different question than Excel.
How to migrate safely
Do not lean on a specific error type. An =ISNA(FILTER(...)) guard catches
LibreOffice's #N/A but not Excel's #CALC!; the portable wrapper is
=IFERROR(FILTER(...),"none"), which catches both. Better still, always pass the
if_empty argument to FILTER so the no-match case never becomes an error in
the first place. For TEXTAFTER/TEXTBEFORE, supplying if_not_found
is not enough on LibreOffice 25.8, so wrap the call: =IFERROR(TEXTAFTER(A1,"-"),"none").
And if a workbook must open on LibreOffice older than 25.8, avoid these text functions entirely and
fall back to LEFT/RIGHT/MID with FIND.
Honest limits
The Excel results, including #CALC!, are Microsoft's documented behaviour, not values
we executed in Excel. Google Sheets supports these functions but is not yet run through our harness,
so that column is left honest.