LEN function in Excel
LEN returns how many characters are stored in a text value. Product codes, names, and IDs all use the same one-argument function. Spaces, dashes, and digits each count as one character.
LEN function syntax and its one argument
LEN returns the number of characters in text. It counts; it does not extract or replace. One argument, one number.
The text to measure, usually a cell reference (A2). A literal works too: LEN("PRD-1023") returns 8. Numbers are coerced to text first, so LEN(1023) is 4.
LEN example: product code length
This LEN example counts every character in a product code, including the dash.
| Product code |
|---|
| PRD-1023 |
| SKU-9 |
| Ana Lee |
This LEN example returns 8 for PRD-1023: the letters P, R, and D, the dash, and the digits 1, 0, 2, and 3. SKU-9 is 5. Ana Lee is 7 because the space between the names counts. LEN does not skip spaces or punctuation.
4 LEN function gotchas
1. Spaces count
"Ana Lee" is 7, not 6. A trailing space and the space between two words both add one. When you want the visible length, trim first: =LEN(TRIM(A2)).
2. LEN reads the stored value
A date is a serial number in the cell. LEN counts the digits of that number, not the 2026-10-13 you see on screen. Wrap the value in TEXT when you need the displayed characters.
3. LEN versus LENB
LEN counts characters, one each, no matter how many bytes they use. LENB counts bytes, so a double-byte character counts as 2. Product codes and names belong in LEN.
4. A blank cell is 0
A truly empty cell returns 0. A formula that returns "" is also 0. That differs from COUNTA, which treats "" as non-blank.
Excel vs Google Sheets
Microsoft Excel (365 / Desktop)
Excel LEN takes one argument in desktop Excel and Microsoft 365. LENB is the byte-count variant. Cross-sheet references work like any other function: =LEN(Sheet2!A2).
Google Sheets
Google Sheets uses the same LEN(text) syntax. Spaces and punctuation each count as one character. LEN("PRD-1023") is 8 there too.
LEN exercise for this guide
LEN exercise
Count product-code characters with LEN. The formula has to follow A2 — a hardcoded 8 will fail the next case.
FAQ
How does the LEN function work in Excel?
=LEN(text) returns the number of characters in text. =LEN(A2) counts whatever is stored in A2. LEN("PRD-1023") is 8: three letters, one dash, and four digits.
Does the LEN function count spaces?
Yes. Every space is one character, so "Ana Lee" is 7, not 6. A trailing space makes the count one higher than the text you see. =LEN(TRIM(A2)) drops leading and trailing spaces before counting.
What is the difference between LEN and LENB?
LEN counts characters. LENB counts bytes and is meant for double-byte character sets, where one character can take two bytes. For product codes and names, use LEN.
Why is LEN longer than the text I see?
LEN counts stored characters, not the formatted display. A trailing space, a non-breaking space, or a number format that hides digits will not match what you read on screen. A blank cell returns 0.