Excel LEN Function
last modified October 5, 2026
The LEN function is a text function in Excel that returns the
number of characters in a text string. It counts every character, including
letters, digits, punctuation, and spaces. This tutorial covers the syntax, the
argument, practical examples, and the most common pitfalls, so you can use
LEN for data validation, text analysis, and cleaning tasks.
LEN Function Basics
The LEN function counts all characters in a text string. Spaces
count as characters, and there is no way to make LEN ignore them.
Numbers and dates are converted to text before they are measured, which is a
common source of surprises.
| Component | Description |
|---|---|
| Function Name | LEN |
| Syntax | =LEN(text) |
| Arguments | text - The string whose length you want to find |
| Return Value | Number of characters in the text string |
This table breaks down the essential components of the LEN
function. It shows the function name, basic syntax format, argument
requirements, and return value characteristics.
| Argument | Meaning |
|---|---|
| text | The string whose length you want to find. It can be a literal, a cell
reference, or an expression such as A1&" "&B1. Numbers
are converted to text automatically. |
LEN takes exactly one argument. An empty string returns 0, a single
space returns 1, and the longest text a cell can hold is 32,767 characters.
Basic LEN Example
This example demonstrates the simplest use of the LEN function
with a direct text string input.
=LEN("Excel")
This formula counts the characters in the word Excel. The result
is 5. This shows how LEN works with hard-coded text values without
cell references.
LEN with Cell References
A more practical use of LEN involves counting characters in cells.
Here's an example with cell references.
| A | B |
|---|---|
| Hello World | =LEN(A1) |
The table shows a simple spreadsheet with text in cell A1 and a LEN
formula in cell B1. The result is 11 because "Hello World" contains 10 letters
plus one space. Spaces are always counted.
=LEN(A1)
Using cell references makes LEN more powerful for working with
spreadsheet data, because the length is recalculated whenever the source text
changes.
Counting Spaces
Whitespace is often invisible, so LEN is the quickest way to prove
that a value is not what it seems. The table below lists the most common
space-related cases.
| Value | =LEN(...) | Reason |
|---|---|---|
| "" (empty string) | 0 | No characters at all |
| " " (one space) | 1 | A space is a character |
| "Hello World" | 11 | 10 letters plus the space between the words |
| " Spaces " | 10 | 2 leading spaces + 6 letters + 2 trailing spaces |
The third row is the classic trap: text that looks like a single word can carry leading or trailing spaces that change comparisons, lookups, and validation.
=LEN(TRIM(A1))
For " Spaces ", TRIM removes the leading and
trailing spaces, so the formula returns 6 instead of 10. Compare the two results
to reveal stray whitespace.
Note that TRIM removes the space character (code 32) but not the
non-breaking space (code 160) that frequently arrives from web pages. Remove
that one with =SUBSTITUTE(A1, CHAR(160), " ") before trimming.
Numbers and Dates as Text
Before counting, LEN converts its argument to text. For a number
it uses the stored value, not the way the cell is formatted. For a date it uses
the underlying serial number.
| Value in A1 | =LEN(A1) | Reason |
|---|---|---|
| 123 | 3 | Three digits |
| 12.5 | 4 | Digits plus the decimal point |
| 1.0 (stored as 1) | 1 | Excel keeps the numeric value 1, not the typed text |
| -123 | 4 | The minus sign is a character |
| 1000 formatted as $1,000.00 | 4 | Formatting is not part of the stored value |
| DATE(2023,1,1) | 5 | The serial number 44927 has five digits |
The distinction between a number and text matters. =LEN(1.0)
returns 1 because Excel stores the number 1, while =LEN("1.0")
returns 3 because the value is text. When the displayed form is what you need,
render it first with TEXT.
=LEN(TEXT(A1, "mm/dd/yyyy"))
For the date 1/1/2023, this returns 10, the length of
01/01/2023. Without TEXT, the same date counts the
serial number and returns 5.
LEN for Validation
A frequent use of LEN is checking that a value has an exact length,
such as an eight-character product code or a ten-digit phone number. The
comparison returns TRUE or FALSE.
| A | B | C |
|---|---|---|
| AB123456 | =LEN(A1)=8 | =IF(LEN(A1)=8, "Valid", "Invalid") |
| AB12345 | =LEN(A2)=8 | =IF(LEN(A2)=8, "Valid", "Invalid") |
The first code passes and the second fails. To count how many values in a range
match a given length, wrap the test in SUMPRODUCT:
=SUMPRODUCT(--(LEN(A1:A10)=8)). The double minus converts the
TRUE and FALSE values to 1 and 0 before they are
added.
=SUMPRODUCT(--(LEN(A1:A10)=8))
LEN vs LENB
LENB counts the number of bytes used to represent the text rather
than the number of characters. In a workbook whose default language uses a
double-byte character set (DBCS), such as Japanese, Chinese, or Korean, a
double-byte character counts as 2 with LENB but as 1 with
LEN.
| Text | =LEN(...) | =LENB(...) |
|---|---|---|
| "abc" | 3 | 3 |
| A double-byte character in a DBCS language | 1 | 2 |
LENB is deprecated, and its result depends on the default language
setting of the workbook, so it is easy to get different answers on different
machines. Use LEN unless you specifically work with legacy
double-byte data.
Combining LEN with LEFT, RIGHT, and MID
LEN is often paired with the extraction functions. It tells them
how much text remains once a known part has been removed.
To drop the last three characters of a code, subtract 3 from the length and hand
the result to LEFT:
=LEFT(A1, LEN(A1)-3)
For AB123456, LEN returns 8, so LEFT
returns the first 5 characters, AB123. The same idea works at the
other end: to keep the characters after the dot in a file name, use
=RIGHT(A1, LEN(A1)-FIND(".", A1)).
=RIGHT(A1, LEN(A1)-FIND(".", A1))
For report.xlsx, FIND returns 7, the position of the
dot, and LEN returns 11, so RIGHT returns the last 4
characters, xlsx. Because LEN adapts to the actual
text, these formulas work for names of any length.
LEN with Empty Cells and Errors
LEN returns 0 for an empty cell, but it cannot hide an error. If
the referenced cell contains an error value, that error is passed straight
through.
| A | B |
|---|---|
| =LEN(A1) | |
| #N/A | =LEN(A2) |
Cell B1 returns 0 because A1 is empty. Cell B2 returns #N/A rather
than a number, because the error in A2 propagates through LEN. Use
=IFERROR(LEN(A2), 0) when you want a numeric result regardless of
the source.
LEN Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Cell looks empty but LEN is not 0 | A number larger than expected | Remove spaces with TRIM, or replace CHAR(160) with SUBSTITUTE |
| Number formatted with symbols | Length is shorter than the display | Render the value with TEXT before counting |
| Date counted by LEN | 5 instead of 10 | Wrap the date in TEXT to count the formatted text |
| Source cell contains an error | The same error is returned | Handle it with IFERROR or test with ISERROR |
| Emoji or other surrogate pair | Count depends on the Excel version | Modern versions count a surrogate pair as one character |
| LENB used in a DBCS workbook | Bytes instead of characters | Use LEN; LENB is deprecated |
The LEN function is a small but essential tool for text work in
Excel. It returns the number of characters in a value, counts spaces and
punctuation, and converts numbers and dates using their stored form. Because it
combines so naturally with TRIM, TEXT,
LEFT, RIGHT, MID, and
SUMPRODUCT, it sits at the centre of validation and cleaning tasks.
Remember the two rules that cause most mistakes: spaces always count, and a
number or date is measured as stored, not as displayed. Keep those in mind and
LEN will give you an exact answer every time.
Author
List all Excel Formulas.