LEN in Excel: Syntax, Examples and Errors
Updated 2026-10-15 · Free · Works in Excel & Google Sheets
Syntax
=LEN(text)Quick answer
LEN counts characters in a cell: =LEN(A2) returns 8 for "jane doe". Every character counts — spaces, punctuation and line breaks included — and LEN itself returns a true number you can compare and sum. A displayed date reports the length of its serial number, not of the date you see.
Syntax
=LEN(text)| Argument | What it means |
|---|---|
| text | The cell, literal or expression to measure. Required — and the only argument. Numbers are measured by their displayed digits; dates by their hidden serial number. |
One argument, no separators to localize: =LEN(A2) is written identically in every Excel language, comma or semicolon.
LEN is the measurer of the text family: it counts every character — letters, digits, spaces, punctuation, line breaks — and returns a genuine number, not text. That makes it the only text function here whose result you can feed straight into comparisons and arithmetic without VALUE.
Basic examples
=LEN(A2)The base measurement: "jane doe" is 8, "AE-2026-041" is 11, "" is 0. A quick LEN column over imported IDs instantly reveals rows that are too short (truncated) or too long (padding).
=LEN(A2)-LEN(TRIM(A2))The space audit: the difference tells you exactly how many characters TRIM would remove. A 0 means the cell is clean, a 3 means three stray spaces hiding before, after or between words.
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1The word counter: removing every space and measuring the shrink gives the space count, and one more is the number of words. Simple and reliable for single-spaced text.
=IF(LEN(A2)>20, "Too long", "OK")The length gate: flags any description, code or username that exceeds a system limit before it hits the import file. Because LEN returns a number, the comparison needs no conversion.
=LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10), ""))The line-break detector: CHAR(10) breaks are invisible on screen but count as characters, so the difference exposes exactly how many breaks are hiding in the cell — the fastest diagnosis before cleaning them out.
A date formatted 2026/10/15 reports LEN = 5, because LEN measures the serial number (45945) underneath, not the displayed text. The same applies to numbers with custom formats — LEN sees the stored value, never the formatting.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| A date returns 5 instead of 10 | LEN measures the stored serial number (45945), not the displayed 2026/10/15. | Measure the formatted text: =LEN(TEXT(A2, "yyyy/mm/dd")). |
| An "empty-looking" cell returns 1 or 2 | The cell is not empty — it holds one or two plain or non-breaking spaces. | Confirm with =LEN(TRIM(A2)) — 0 after TRIM means only spaces were present. |
| The count does not match what you see | Invisible characters count: line breaks CHAR(10) and non-breaking spaces CHAR(160) each add 1. | Detect breaks with =LEN(A2)-LEN(SUBSTITUTE(A2, CHAR(10), "")); clean the cell afterwards. |
| CJK text seems to have the wrong length | LEN counts characters, so 毎 character — full-width included — is 1. Byte-based systems expect 2 per CJK character. | Use LENB, which counts 2 per double-byte character on DBCS settings. |
| The result cannot be compared with 10 | Rare — LEN already returns a true number. If the comparison fails, the "number" is actually text from an import. | Check with =ISNUMBER(LEN(A2)); if FALSE, the cell measured was itself text-formatted — wrap the source in VALUE first. |
Not an error, but worth knowing: LEN sees stored values, not formatting. It cannot tell text "12345" from number 12345 — both measure 5 — and it never rounds, truncates or skips whitespace.
LEN vs. LENB
| LEN | LENB | |
|---|---|---|
| Counts | Characters — every character is 1, full-width or not | Bytes — 1 per single-byte character, 2 per double-byte (CJK) |
| "jane doe" is | 8 | 8 |
| "東京都" is | 3 | 6 on DBCS settings |
| Typical job | Length limits, audits, word counts in Western text | Byte-based imports, legacy systems, CJK field validation |
Pick by consumer: modern systems and most imports want LEN; legacy mainframe or fixed-byte layouts want LENB. On single-byte locales the two return identical results, which is why LENB stays unknown in the West.
LEN vs. Flash Fill
| LEN formula | Flash Fill (Ctrl+E) | |
|---|---|---|
| Output | A live formula in a helper column; recalculates when the source changes | Static values, written once; new rows need a refill |
| Precision | Deterministic — exact counts every time | Pattern-guessed — spot-check the result column |
| Best for | Audits, validations and reports that must stay current | One-time extractions whose lengths you then verify with LEN |
| Available in | Every Excel version and Google Sheets | Excel 2013 and later; Sheets answers with Smart Fill |
The two cooperate: Flash Fill extracts codes from messy cells, then a LEN column proves every extraction is the same length before you trust it. Fill guesses, LEN verifies.
LEN in Google Sheets
- Same name, same single argument — every formula above pastes into Sheets unchanged.
- LENB exists in Sheets with the same byte-counting behavior for double-byte text.
- The line-break detector works identically — Sheets cells store CHAR(10) breaks the same way.
- Dates behave the same: LEN reports the length of the underlying serial number, not the displayed format.
Limitations and pro tips
- LEN measures stored values, not formatting: a date is its serial number, a number is its digits — "2026/10/15" displays 10 characters but measures 5.
- Every character counts: CHAR(10) breaks, CHAR(160) non-breaking spaces and tabs all add 1 — use LEN differences to expose them before cleaning.
- LEN returns a true number, so it needs no VALUE — but the cell it measures may be text; wrap the source in VALUE when digits must behave as numbers.
- For CJK data feeding byte-limited systems, switch to LENB — 2 bytes per double-byte character — before the import rejects your file.
- Combine audits in one column: =LEN(A2)-LEN(TRIM(SUBSTITUTE(A2, CHAR(10), " "))) counts every invisible character at once.
Think of LEN as the inspection gauge of a cleaning line: it changes nothing, it only measures — padding, breaks, truncation. Run it before and after each cleaning stage and the difference tells you whether the stage did its job.
Related Tools
Related Functions
Frequently Asked Questions
What does the LEN function do in Excel?
It counts characters: =LEN(A2) returns 8 for "jane doe". Spaces, punctuation and line breaks all count, and the result is a true number you can compare and sum directly.
Why does LEN return 5 for a date like 2026/10/15?
A date is stored as a serial number — 45945 — and LEN measures that stored value, not the displayed text. Measure the formatted string instead: =LEN(TEXT(A2, "yyyy/mm/dd")) returns 10.
How do I count the number of words with LEN?
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1: removing the spaces shrinks the length by one per space, so the difference plus one equals the word count. Reliable for single-spaced text.
Why does an empty-looking cell return LEN of 1 or 2?
The cell holds invisible characters — plain or non-breaking spaces. Run =LEN(TRIM(A2)): if it drops to 0, the cell contained nothing but spaces, which is exactly why lookups were failing.
What is the difference between LEN and LENB?
LEN counts characters — every character is 1. LENB counts bytes — 2 per double-byte character on DBCS settings, so "東京都" is 3 for LEN and 6 for LENB. Use LENB when the receiving system measures bytes.
Can LEN distinguish text "12345" from the number 12345?
No — both measure 5, because LEN sees stored characters, not data types. Check the type separately with ISNUMBER, or convert digit-text with VALUE before comparing or summing.
Does LEN work in Google Sheets?
Yes — same name, same single argument, same results. LENB is also available, the line-break detector behaves identically, and dates report their serial-number length just like in Excel.
Ready to try it on your own file?
Open Remove Line Breaks from Excel — It's Free