CHAR(0) makes an invisible character in LibreOffice instead of an error

Microsoft documents CHAR as taking “a number between 1 and 255”, and UNICHAR's reference page is explicit that a number of zero or less returns the #VALUE! error. LibreOffice Calc does not reject zero. It builds the character for code point 0 — the NUL control character — and hands it back as an ordinary string. When our harness read the recalculated workbook, that cell came back as the literal seven-character text _x0000_, which is the escape the .xlsx format uses to store a NUL inside a string.

Executed results

The Excel column is the documented-expected value from our test corpus; we did not run Excel. The LibreOffice column is what our harness read back after recalculating the workbook in LibreOffice Calc 25.8.7.3. Read the table as a whole and the shape of the bug is clear: LibreOffice does range-check these functions — =CHAR(256) and =UNICHAR(-1) both return #VALUE!, matching Excel — and it handles ordinary code points, accents and even supplementary-plane emoji identically. Zero is the single value that slips through the check.

A note on what _x0000_ means

_x0000_ is not a piece of text LibreOffice invented. It is the standard OOXML escape for a control character that cannot appear literally in the XML of an .xlsx file, so a cell whose value is a NUL character is written to disk as _x0000_ and that is what our harness read. The practical takeaway is the same either way: the formula produced a character, not an error. In the LibreOffice UI you would see an empty-looking cell; in a file handed to another program you get either a NUL byte or the literal escape text, and both are trouble.

Consistent across LibreOffice versions

=CHAR(0) and =UNICHAR(0) produced the same result in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3, and the two out-of-range rows returned #VALUE! in all four builds. Long-standing behaviour, not a recent change.

Why it happens

Excel's range check is inclusive of the documented lower bound: 1 is the smallest valid argument, so 0 is rejected. LibreOffice's check evidently starts below that, so 0 maps to U+0000 and is returned like any other character. The divergence only surfaces when the argument is computed rather than typed — =CHAR(A1) where A1 is empty, or =CHAR(CODE(x)-64) on an unexpected input, or a lookup that returns nothing. In Excel that mistake shows itself; in LibreOffice it disappears into a column of apparently blank cells.

How to migrate safely

Guard the argument rather than trusting the function to reject it: =IF(n<1,"",CHAR(n)) gives both engines the same answer and makes the intent explicit. Where you are cleaning up data that may already contain these characters, CLEAN is the tool for the job — it strips control characters, and our executed run confirms =CLEAN("Hello"&CHAR(7)&"World") returns HelloWorld in LibreOffice 25.8.7.3. We have not executed CLEAN against a NUL specifically, so verify that case in your own file before relying on it; =SUBSTITUTE(x,CHAR(0),"") is the direct alternative.

When auditing, look for the symptom rather than the formula: a text column where =LEN(cell) disagrees with what you can see, or a key column where lookups miss on rows that look identical, is the fingerprint of an invisible character. And if you have ever seen the literal string _x0000_ appear in an exported file, this class of formula is where it comes from.

Honest limits

The Excel column is Microsoft's documented behaviour recorded in our test corpus, not values we executed in Excel. Google Sheets is not yet run through our harness, so that column is left honest. The LibreOffice column is executed output from LibreOffice Calc 25.8.7.3, reproduced in our three earlier version runs; the exact rendering of the NUL character depends on how the reading program handles it, and _x0000_ is what our .xlsx reader reported.

Check before you migrate