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.

Executed results

The FILTER rows use A1:A3 = 1, 2, 3 and B1:B3 = 1, 2, 3.

The Excel column is the documented-expected result from our test corpus; we did not run desktop Excel. The LibreOffice and Google Sheets columns are executed output (Sheets via the 2026-08-29 plain-name run). Note the pattern: supplying the fallback fixes FILTER in LibreOffice (both return "none"), but Google Sheets ignores the fallback entirely — our executed run shows both the with-fallback case (FILTER_all_false_with_default) and the without-fallback case (FILTER_all_false_no_default) returning #N/A, so passing if_empty does not prevent the error there the way it does in LibreOffice or as Excel documents. Neither engine we tested honours TEXTAFTER/TEXTBEFORE's fallback, and a plain no-match TEXTBEFORE without a fallback agrees on #N/A across Excel, Sheets and LibreOffice.

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 was executed on 2026-08-29 via Drive import using plain (unprefixed) function names — a follow-up run superseding an earlier pass where these same formulas were stored as _xlfn._xlws.FILTER and came back inconclusive because Google’s importer did not map that prefix. With that resolved, both FILTER rows are a real result, not an artifact: Google Sheets returns #N/A for an empty FILTER regardless of whether an if_empty argument is supplied (case ids FILTER_all_false_no_default and FILTER_all_false_with_default), differing from both Excel's documented behaviour and from LibreOffice, which does honour the fallback. The TEXTBEFORE and TEXTAFTER rows returned #NAME?, and that remains a real result: Google Sheets does not have those two functions at all.

Check before you migrate

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.