LENB(“日本”) is 4 in Google Sheets and LibreOffice — and 2 in Excel for the web

LENB is supposed to count bytes where LEN counts characters. For "Phoenix, AZ" they are both 11 and nobody thinks about it. The whole point of the function is what happens with Japanese, Chinese or Korean text, where a character is not a byte — and that is exactly where the documented rule and the executed behaviour part company.

We ran =LENB("日本") — two CJK characters, no spaces, nothing else — in every engine we execute. LibreOffice Calc 25.8.7.3 returned 4. Google Sheets returned 4. Excel for the web returned 2 (executed 2026-09-01), which is what Microsoft’s documented rule gives for the non-DBCS default language our harness runs under. Desktop Excel we do not run at all, so the 2 is a measurement of the web engine, under our account’s language settings, and of nothing else.

4 is not the UTF-8 byte count

It is worth being precise about which wrong answer this is, because “LENB counts raw bytes” is the natural guess and it does not fit the measurement.

The string 日本 encodes as 6 bytes in UTF-8 (three per character) and 4 bytes in UTF-16 (two per character). No engine returned 6. LibreOffice and Google Sheets both returned 4. So what those two are counting is not the storage the file actually uses — both store text as UTF-8 — but two bytes per character, which is the DBCS rule applied unconditionally rather than only under a DBCS default language. Excel for the web’s 2 is not a byte count of anything either: it is the plain character count, which is exactly what the documented rule prescribes outside a DBCS default language.

That matters for a migration because it changes what the number means. A LENB result of 4 is not a promise about how many bytes anything occupies anywhere; it is a character count multiplied by two for the characters an engine considers wide. If you are sizing a database column, a fixed-width export record or an API payload against real UTF-8 bytes, LENB is not measuring the thing you think it is measuring in any engine we executed — not the ones that returned 4, and not the one that returned 2.

Executed: the LENB corpus

All six LENB cases, run as written. The desktop-Excel column is Microsoft’s documented behaviour as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. The Excel for the web column is executed output from the dated OneDrive run of 2026-09-01, recalculated on open and downloaded again for readback; it is a measurement of the web application, which is a different implementation from the desktop one. The Google Sheets column is executed output from the dated Drive-import run of 2026-08-31. The LibreOffice column is Calc 25.8.7.3, with 24.2.0.3, 24.8.7.2 and 25.2.0.3 returning the identical value on every row. The divergence row is the corpus case LENB_non_ascii_characters.

Five of the six rows agree everywhere — documented column and all three executed engines alike — and they are the rows where the DBCS rule cannot change the answer: every character in "Phoenix, AZ", " One " and the coerced number 1234 is single-byte under either reading, an empty string has no bytes under either, and an error argument propagates before LENB ever sees a character. The single CJK row is the whole divergence.

Two smaller things the table settles. Spaces count — leading and trailing — so " One " is 8 and not 3, in all three executed engines as documented. And a numeric argument is converted to its text form first, so =LENB(1234) is 4 rather than an error or a byte width of the underlying double.

For context on whether this batch of text functions is broadly trustworthy: T, the other text function executed in the same 2026-08-31 run, matched the documented result on all six of its cases in Google Sheets and LibreOffice (and on all six again in Excel for the web, executed 2026-09-01) — including the two that catch a naive implementation, =T(19) and =T(TRUE), which both correctly returned empty text rather than "19" or "TRUE". The LENB divergence is a specific finding, not noise from a bad run.

Microsoft has deprecated LENB

There is a second reason not to build on this function, and it is on the help page itself. The current live LEN/LENB article states that the LENB function is deprecated, and it has dropped the DBCS explanation entirely — the “2 bytes per character only when a DBCS language is set as the default language” wording that our test case is measured against now survives only in the archived version of the page.

So the documented behaviour this page compares against is documentation Microsoft has retired. That cuts both ways honestly: it means our desktop-Excel column for the CJK row rests on an archived rule rather than a current one, and it means a workbook whose logic depends on LENB is depending on a function its own vendor has stopped explaining. Both are reasons to migrate away from it rather than to reason carefully about it.

What this means for a CJK migration

The risk depends on the locale the workbook was built under, not on the workbook. This is the unusual part. If the original Excel file was authored and run under a Japanese, Simplified Chinese, Traditional Chinese or Korean default language, then under the documented rule Excel counted 2 bytes per CJK character — the same 4 that LibreOffice and Google Sheets returned for us. Those workbooks migrate without a change in value. If the original ran under a non-DBCS default language such as en-US, the documented rule gives 1 byte per character, and every LENB over CJK text doubles on migration. The formula is identical in both cases; only the machine it used to run on differs.

Excel for the web behaved like the non-DBCS reading for us, and that is a data point, not a rule. Our run returned 2, i.e. one byte per CJK character, on one account whose language settings we did not vary. It is a fair prediction that a workbook moving to Excel for the web under similar settings keeps the non-DBCS answer — and it is not a guarantee, because we did not test a DBCS-configured account, and it says nothing about desktop Excel on a Japanese-, Chinese- or Korean-locale machine, which we do not run.

The failure is silent and directional. A doubled byte count does not error. It overstates width, so the typical symptoms are a validation rule that starts rejecting valid input, a “field too long” flag that fires on rows that were fine yesterday, a truncation LEFT/MID that cuts short, or a padding calculation that pads to the wrong width in a fixed-width export.

Find the exposure by searching for the B-suffix family. LENB is the one we executed, but Excel ships a whole set of byte-oriented twins — LEFTB, RIGHTB, MIDB, FINDB, SEARCHB, REPLACEB — that carry the same DBCS wording. None of those has a case in our corpus, so we make no claim at all about how any engine implements them; we mention them because a workbook that uses one usually uses several, and they are what you should be grepping for.

Prefer LEN unless you genuinely need bytes. Most real uses of LENB in the wild are counting characters for a display or a validation and reaching for the byte version out of habit or copied code. LEN is portable, current, and means one thing everywhere.

If you genuinely need UTF-8 bytes, compute them. No engine’s LENB gave us the UTF-8 byte count in this test, so a workbook that needs real byte budgets has to build the number rather than ask for it — typically by classifying characters and weighting them. We have no executed case for such a construction and are not going to publish one as though we had; the point is only that LENB is not it.

Honest limits

The desktop-Excel column is Microsoft’s documented behaviour as recorded in our test corpus, and for the CJK row that documentation is the archived LEN/LENB page — the current page has removed the DBCS rule and marks the function deprecated. We do not run desktop Excel, and no value in that column is a measurement.

What the Excel-for-the-web number is, and what it is not. On 2026-09-01 we executed all six LENB cases in Excel for the web — uploaded to OneDrive, recalculated on open, downloaded again for readback — and the CJK case (LENB_non_ascii_characters) returned 2. All six matched the documented expectation. That is evidence about the web calculation engine, and about our account’s language settings, and about nothing else. Excel for the web is a separate implementation from desktop Excel; a single run on one account and one locale does not establish what desktop Excel returns anywhere, and in particular says nothing about what desktop Excel returns on a machine whose default language is Japanese, Simplified or Traditional Chinese, or Korean — the case where the documented rule itself changes the answer to 4.

So the locale caveat survives intact. Microsoft documents desktop Excel’s LENB as depending on the default language / DBCS setting, and we have not varied that setting in any engine. The honest statement is narrow: under our account’s locale, Excel for the web returned 2; under our VM’s non-DBCS locale, Google Sheets and all four LibreOffice builds returned 4. Whether desktop Excel agrees with either, and whether any of the three change under a DBCS default language, remains unmeasured.

The locale question cuts into our own results too, and this is the biggest caveat on the page. Our LibreOffice runs happen on a VM whose default language is en_US.UTF-8, which is what makes the documented answer 2 rather than 4 for that row. We did not re-run the corpus under a DBCS default language, so we cannot say whether LibreOffice’s answer would change if we did — only that under a non-DBCS default it returns 4 where the documented rule says 1 byte per character. For Google Sheets the document and account locale are Google’s to decide and we did not control them, so treat the Sheets 4 as “what the Drive-import run returned” and not as a locale-controlled experiment. The same applies to the Excel-for-the-web 2: it is what the OneDrive round trip returned on our account, not a controlled locale experiment.

Since this page was written the rest of the B-suffix family has been executed too — LEFTB, RIGHTB, MIDB, FINDB, SEARCHB and REPLACEB, on 2026-08-31 in Google Sheets and LibreOffice and on 2026-09-01 in Excel for the web. Each carries a full-width-character probe, and they corroborate the split above rather than complicating it. =LEFTB("ＥＸＣＥＬ",2) returned Ｅ and =RIGHTB("ＡＢＣ",2) returned Ｃ in Google Sheets and LibreOffice — two bytes spent on one full-width character, exactly the 2-bytes-per-character rule that makes LENB("日本") come out as 4 there. The same two formulas returned ＥＸ and ＢＣ in Excel for the web — two full-width characters for two bytes, i.e. one byte each, exactly the non-DBCS reading that makes its LENB("日本") come out as 2. The two families of results are internally consistent within each engine. Those probes carry no asserted expected value in our corpus — they are published as observations, not as matches or mismatches.

Scope. The headline LENB finding is still one probe, on one two-character string, in one function, run once per engine. Our LEN cases are all ASCII, so we have no executed value for =LEN("日本") to sit beside the LENB one. Mixed ASCII-and-CJK strings, emoji and other astral-plane characters, combining marks, and non-CJK non-ASCII text are all untested here.

The Google Sheets column is executed output from a dated run, because Sheets has no version number to pin: a formula-only .xlsx carrying no cached results goes to Google Drive, is recalculated by Google’s engine, and comes back as .xlsx for readback, with a deterministic =1111+2222 canary in every sheet proving the recalculation happened. These six cases ran on 2026-08-31. See our methodology for how recalculation is proven rather than assumed, and the function pages for LENB, LEN and T for the per-engine support matrices.

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.