The Excel LEN function, explained interactively.
LEN returns the character count of a text value. Simple on the surface, but LEN is the cheapest diagnostic you have for messy data — comparing LEN(A2) with LEN(TRIM(A2)) finds hidden whitespace, and wrapping numeric values surfaces coercion behaviour you’ll run into with every other text function.
How to use LEN
- Type
=LEN(and supply the text — a cell reference, a literal string, or a formula that returns text. - Close the parenthesis. LEN returns a single integer — the count of characters in the coerced-to-text value.
- To diagnose hidden whitespace, compare
LEN(A2)withLEN(TRIM(A2)). Any gap is leading, trailing, or repeated internal spaces that TRIM will strip. - On numeric cells, LEN counts the General-formatted string. Wrap with
TEXT(A2, "0.00")first if you need the length of the displayed value.
In the demo below, spaces are rendered as middle dots (·) so you can see what LEN is counting. Row 4 shows hidden padding; row 5 shows a number coerced to text; the CJK row shows Unicode behaviour.
LEN(12345.67) returns 8, the length of the string "12345.67".| A | B | |
|---|---|---|
| 1 | Source (· = space) | Length |
| 2 | Hello | 5 |
| 3 | Excel·LEN | 9 |
| 4 | ··hello·· | 9 |
| 5 | 12345.67 | 8 |
| 6 | café | 4 |
| 7 | 你好世界 | 4 |
| 8 | (blank) | 0 |
=LEN(A2). Notice row 4 (··hello··): LEN counts 9, not 5 — the two leading and two trailing spaces are part of the string. Row 5 shows LEN coercing the number 12345.67 to the text "12345.67" before counting (8 chars including the dot). The CJK row returns 4 in modern Unicode Excel — the byte-counted equivalent is LENB.LEN syntax and arguments
One required argument, no optionals. See Microsoft’s official LEN / LENB reference for the canonical specification.
A2), literal ("hello"), or any formula that resolves to a value. Numeric inputs are coerced to text via General formatting first — LEN(100) returns 3, not an error. Blank cells return 0.LEN examples
Four patterns that cover most real-world LEN use.
Example 1: LEN — basic character count
The simplest case — count characters in a text value, spaces and punctuation included.
Returns 5 for "Hello", 9 for "Excel LEN" (space counts), 0 for a blank cell. Works on any scalar value — numbers are coerced to their General-format text first.
Example 2: LEN + TRIM — find hidden whitespace
The canonical diagnostic. Subtract the trimmed length from the raw length; the difference is the hidden padding to worry about.
Returns 4 for " hello " — two leading spaces plus two trailing. A non-zero result flags the row for cleanup. Use IF(... > 0, "dirty", "clean") to surface them in bulk.
Example 3: LEN — validate fixed-length codes
Test that identifiers meet an expected character count. Useful for SKUs, ISBNs, phone numbers, and any fixed-width field.
ISBN-13s are exactly 13 characters (ignoring dashes). Combine with SUBSTITUTE to strip dashes before counting: =LEN(SUBSTITUTE(A2, "-", ""))=13. LEN-based validation is faster than regex and more legible than nested IFs.
Example 4: LEN — count occurrences of a character
A classic idiom: compute how many times a character appears by diffing against the SUBSTITUTE-stripped length.
Returns the number of dashes in A2. Replace "-" with any single character; for case-sensitive vs insensitive, use SUBSTITUTE (sensitive) or Excel 365’s longer-form TEXTSPLIT chain.
Common LEN errors and fixes
Four failure modes, each with what to check and how to recover.
LEN returns a bigger number than expected
Cause: hidden leading / trailing spaces, usually from a CSV import or a web-form paste. LEN counts every character, not just the visible ones.
Diagnose with =LEN(A2) - LEN(TRIM(A2)). If the gap is positive, TRIM the column or use Excel’s Find & Replace with CHAR(160) (non- breaking space) as the target — TRIM doesn’t strip NBSP, a common import artefact.
LEN on a number gives the wrong length
Cause: the cell is formatted (e.g. $1,234.56) but stored as the number 1234.56. LEN sees the underlying value, not the displayed string.
Wrap with TEXT to lock the format: =LEN(TEXT(A2, "$#,##0.00")). Use the exact format code you see in the cell, or the length will still mismatch the display.
LEN returns #VALUE!
Cause: the cell contains an error value like #N/A or #DIV/0!. LEN propagates errors — it doesn’t silently coerce them.
Wrap with IFERROR: =IFERROR(LEN(A2), 0). Returns 0 for error rows and the correct length for the rest.
LEN undercounts emoji and non-BMP characters
Cause: Excel counts UTF-16 code units, not user-perceived characters. A single emoji like 🏳️🌈 can be many code units (multiple BMPs plus combiners).
For visual character counting, there’s no built-in — this is a documented limitation. If your data has emoji, handle them upstream or use Power Query / Python to pre-compute lengths. ASCII / Latin / CJK text is unaffected.
LEN vs LENB, TRIM & FIND
Four text utilities that often appear together. Pick by whether you’re counting, cleaning, or locating.
| Function | Does what | Args | Typical use |
|---|---|---|---|
| LEN | Counts characters | text | Length check, fixed-width validation, hidden-space diagnostic |
| LENB | Counts bytes | text | Byte-oriented legacy formats, DBCS interop |
| TRIM | Strips leading/trailing + collapses internal spaces | text | Import cleanup, frequently paired with LEN as a diagnostic |
| FIND | Returns position of a substring | find_text, within_text, [start] | Locate delimiters before splitting with LEFT/MID/RIGHT |
Rule of thumb: LEN is the measuring tape — pair it with TRIM to diagnose whitespace, with SUBSTITUTE to count occurrences, with FIND to validate format, with LEFT / RIGHT to bound a split. LENB only matters for byte-oriented legacy files; in modern Unicode Excel, reach for LEN 99% of the time.
LEN frequently asked questions
6.01Does LEN count spaces in Excel?▸
Yes — LEN counts every character including spaces, tabs, and non-breaking spaces. That’s why =LEN(A2) often returns a bigger number than you expect: hidden trailing spaces from a CSV import inflate the count. Wrap with TRIM to measure the visually-meaningful length: =LEN(TRIM(A2)).
6.02How do I find hidden spaces with LEN?▸
Compare =LEN(A2) with =LEN(TRIM(A2)). If the two differ, A2 has leading, trailing, or repeated internal spaces. The gap tells you how many whitespace characters TRIM will strip — a quick diagnostic for messy imports.
6.03Does LEN work on numbers in Excel?▸
Yes, but LEN first coerces the number to text using General format. LEN(12345.67) returns 8 — the length of the string "12345.67" including the decimal point. Apply formatting with TEXT() first if you want the length of the displayed string rather than the underlying General form.
6.04Why does LEN return different numbers on CJK text in different Excel versions?▸
Modern Excel (365 / 2021+) uses Unicode, so LEN counts each CJK character as one. Legacy DBCS Excel builds counted each double-byte character as 2 — in those builds LENB is what you want for byte-accurate counts. In new files, prefer LEN for character counts, LENB only when interoperating with byte-oriented legacy systems.
6.05What’s the difference between LEN and LENB?▸
LEN counts characters; LENB counts bytes. They return the same number for ASCII text. On double-byte text (CJK, some emoji) LENB returns roughly double what LEN returns. In modern Unicode Excel LEN is almost always what you want — LENB only matters for fixed-byte-width legacy formats.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.