TRIM and non-space whitespace: tabs and line feeds vanish in Google Sheets, survive in Excel and LibreOffice

TRIM is the function everyone reaches for after pasting data out of a web page or a text file. Microsoft is explicit about what it does: it “was designed to trim the 7-bit ASCII space character (value 32) from text” — the ordinary space, and nothing else. LibreOffice Calc follows that rule exactly. Google Sheets does not. In Sheets a leading tab (CHAR(9)) or line feed (CHAR(10)) is stripped by TRIM along with the spaces, so the same formula over the same text returns a string of a different length depending on which application opened the file.

A minimal example

Put a tab in front of a word and ask how long the trimmed result is. Six means the tab survived; five means it was stripped.

Row three is the clearest reading of the divergence: CODE of the first surviving character is 9 (the tab itself) in LibreOffice and 72 (the letter H) in Google Sheets. The last row is the control — with ordinary spaces only, every engine collapses a string of two spaces, a, two spaces, b, two spaces down to a b, length 3. What diverges is non-space whitespace, not TRIM’s core behaviour.

The Excel, desktop column is Microsoft’s documented behaviour recorded in our test corpus, not a measurement. The Excel for the web column is our dated recalculation run; a dash means that formula is not in the corpus the web run covered, so we have no measurement to publish and are not guessing one. The Google Sheets figures marked “executed” come from our dated Drive-import run; the ones marked “hand-checked” were typed into a live Google sheet on 2026-09-04 and read back, which is weaker provenance than a harness run and is labelled so. The LibreOffice column is what our harness read back after recalculating the workbook, and 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 all returned the same values, so this is settled behaviour rather than a version difference.

Non-breaking spaces, and the invisible characters nobody trims

The non-breaking space (U+00A0, the HTML  ) is the character that actually survives everywhere. Microsoft says so outright: “by itself, the TRIM function does not remove the nonbreaking space character (which has a decimal value of 160)”. Our executed runs agree, and so does the zero-width space (U+200B):

Read the last row before the ones above it, because it changes what they mean. On our Linux build LibreOffice’s CHAR(160) does not produce a non-breaking space at all: it produces U+FFFD, the Unicode replacement character. LibreOffice’s own help defines CHAR as converting “a number into a character according to the current code table”, so this is a code-table effect and may well differ on another platform or locale — we have executed it on one Linux machine, across all four builds, and that is the extent of what we know. Excel’s CHAR maps through the Windows-1252 code page, where 160 is the non-breaking space, which is why Microsoft can call CHAR(160) the nbsp character; Google Sheets matches Excel here.

So the corpus row where LibreOffice appears to mangle TRIM(CHAR(160)&…) is not a TRIM bug. Feed LibreOffice a real non-breaking space with UNICHAR(160) and TRIM leaves it alone exactly as Excel documents: length 7, first code point 160, identical in all four builds. UNICHAR is the portable spelling; CHAR is not.

The portable fix

Do not ask TRIM to normalise anything but spaces. Convert the non-breaking spaces to real spaces first, strip the control characters with CLEAN, then let TRIM do its one job:

=TRIM(CLEAN(SUBSTITUTE(A1,UNICHAR(160)," ")))

Each layer is doing something the others cannot. SUBSTITUTE handles the non-breaking space, which no engine trims. CLEAN removes ASCII codes 0 through 31, which covers both the tab and the line feed — our corpus case =CLEAN(CHAR(9)&"Hello") returned Hello in every engine we run, including all four LibreOffice builds, so this is the part of the chain that behaves the same everywhere. TRIM then collapses the spaces, which is the one thing it is defined to do.

Two caveats. UNICHAR needs Excel 2013 or later; it is available in Google Sheets and in every LibreOffice build we test. If you must support older Excel, write SUBSTITUTE(A1,CHAR(160)," ") instead and accept that on LibreOffice the CHAR(160) argument depends on the code table, as the table above shows. And CLEAN does not touch the zero-width space (U+200B), because U+200B is not an ASCII control character — add SUBSTITUTE(…,UNICHAR(8203),"") if your source data has them.

What each app documents

Microsoft’s TRIM reference is the precise one: the 7-bit ASCII space, value 32, and a separate paragraph warning that the non-breaking space is not removed. Google’s TRIM documentation says only “Removes leading, trailing, and repeated spaces in text” — it does not mention tabs or line feeds anywhere, so the stripping we measured in Sheets is undocumented behaviour, not a documented extension. LibreOffice documents TRIM as removing spaces and matches Microsoft. That asymmetry is worth knowing when you decide which engine to treat as the reference: the behaviour you can rely on across all three is the narrow one.

Function pages with the full executed matrix: TRIM, CHAR, CLEAN, SUBSTITUTE, UNICHAR.

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); it kept the tab and both non-breaking spaces, matching documented desktop Excel and LibreOffice, and unlike Google Sheets. Only the two corpus cases were covered by that run, which is why the other rows in its column are dashes. 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.