Excel LEN Function

LEN counts the number of characters in a piece of text and returns that count as a number. It counts everything: letters, digits, spaces, punctuation, even a leading space you can't see in the cell. That last part is what makes LEN so useful for catching messy data, and also where most people get tripped up, because a cell that "looks" right can still return the wrong length.

Syntax

=LEN(text)

Parameter	Required	Description
text	Yes	The text string or cell reference you want to measure. Accepts a literal string in quotes, a cell reference, or a number. If the cell holds a date or a formatted number, LEN measures the underlying value, not the formatting you see.

Basic Example

You're auditing a list of product SKU codes in column B, and every code is supposed to be exactly 8 characters.

=LEN(B2)

// B2  = the SKU code you're checking

LEN(B2) counts every character in B2 and returns that count as a number. If B2 contains "SKU-1234", LEN returns 8. If someone copy-pasted "SKU-1234 " with a trailing space, LEN returns 9, and now you have your answer for why that row failed validation somewhere else in the sheet.

How LEN Works

LEN counts every character literally

Letters, numbers, spaces, and punctuation all count equally. =LEN("Excel 365!") returns 10, because the space and the exclamation point each count as one character, just like any letter.

Number formatting is ignored

LEN measures the underlying value, not what's displayed. A cell formatted as currency showing "$1,000.00" is really just the number 1000, so =LEN(A2) on that cell returns 4, not 10. Dates behave the same way: Excel stores dates as serial numbers, so a cell showing 1/15/2027 might actually return 5, because the underlying serial number has 5 digits.

Empty cells and empty strings return 0

=LEN(A2) returns 0 if A2 is truly empty. It also returns 0 if A2 contains an empty string produced by a formula, such as ="". This is a useful, quiet way to test whether a cell has real content.

Quoted text in a formula doesn't count the quotation marks

When you type a literal string like =LEN("Report"), the quotation marks are formula syntax, not characters. LEN returns 6, counting only R-e-p-o-r-t.

Common Use Cases

Validate that entries meet a required length

Flag any SKU code in column B that isn't exactly 8 characters.

=IF(LEN(B2)=8, "OK", "Check length")   // flags anything that isn't 8 characters

Count how many times a specific character appears

Count the number of times the letter "e" appears in a product description.

=(LEN(B2)-LEN(SUBSTITUTE(B2,"e","")))/LEN("e")

// LEN(B2)                         = total length of the string
// LEN(SUBSTITUTE(B2,"e",""))       = length after removing every "e"
// the difference, divided by 1     = number of "e" characters found

Count words in a cell without a helper column

Count the words in a customer feedback comment stored in cell B2.

=LEN(TRIM(B2))-LEN(SUBSTITUTE(TRIM(B2)," ",""))+1

// TRIM(B2) strips extra spaces so double spaces don't inflate the count
// removing every space and comparing lengths gives the number of gaps
// +1 converts gaps between words into a word count

If you're on Excel 365, =COUNTA(TEXTSPLIT(TRIM(B2)," ")) does the same job with less nesting. The LEN version above still matters if you're building for a shared workbook that has to work in older Excel versions.

Count total characters across an entire range

Add up the total characters typed across 50 survey responses in B2:B51.

=SUMPRODUCT(LEN(B2:B51))   // sums the character count of every cell in the range

SUMPRODUCT forces LEN to evaluate across the whole range and add the results, without needing to enter it as an array formula.

Handling Errors

LEN rarely fails on its own. When it does throw an error, it's almost always because the cell it's pointing at already contains one.

Common causes:

• The referenced cell contains an error value like #N/A or #REF!, and LEN passes that error straight through
• You've deleted or moved the source cell, so the reference is broken
• You're feeding LEN a merged cell or a reference outside your actual data, returning an unexpected result rather than a clean error

Don't reach for IFERROR by default here. Wrapping the formula as =IFERROR(LEN(B2), "Check source cell") replaces any error, including a genuine #REF! from a broken reference, with the same generic fallback text. That hides the actual cause instead of fixing it. Trace the error back to B2 first: check whether the reference moved, the source data was deleted, or the cell itself holds an error carried over from an earlier formula. Only add a fallback once you've confirmed the error is expected, not a sign of broken data upstream.

Notes & Gotchas

What does the LEN function do in Excel?

LEN returns the number of characters in a text string or cell, counting letters, numbers, spaces, and punctuation as a single number. It's the standard way to measure text length in a formula rather than eyeballing a cell.

How do you use the LEN function in Excel?

Type =LEN( followed by the cell reference or text you want to measure, then close the parenthesis. =LEN(A2) measures whatever is in A2, and =LEN("Excel") measures the literal text "Excel," returning 5.

What is the syntax for LEN in Excel?

The syntax is =LEN(text), where text is the only argument. It accepts a cell reference, a literal string in quotes, or a number, and always returns a single count.

Does LEN count spaces in Excel?

Yes. LEN counts every space exactly like it counts a letter or digit. A trailing or leading space you can't see will still add to the total, which is why LEN is often used to catch invisible formatting problems in imported data.

How do you count characters in Excel using a formula?

Use =LEN(cell) for a single cell, or =SUMPRODUCT(LEN(range)) to total the characters across many cells at once. For a count that ignores spaces, subtract a space-stripped version: =LEN(A2)-LEN(SUBSTITUTE(A2," ","")).

Why does LEN return the wrong length for a date or number?

Because LEN measures the underlying stored value, not the formatted display. A date showing "01/15/2027" is stored as a serial number, and LEN returns the length of that serial number, not the length of the text you see on screen. Convert the value to text first with =LEN(TEXT(A2,"mm/dd/yyyy")) if you need the length of the displayed format.

Why does LEN return 0 for a cell that looks like it has text?

The cell likely contains a formula that returns an empty string (=""), or it only appears to have content because of formatting, conditional formatting, or a leftover value that was deleted but not cleared properly. Click into the cell and check the formula bar to confirm what's actually there.

Does LEN work with emoji or non-English characters?

LEN counts most single characters correctly, including accented letters and characters from non-Latin alphabets. Some emoji and certain combined characters are stored as more than one underlying code unit, so an emoji that looks like one character on screen can return a LEN result of 2. If exact character counts matter for multi-byte content, test the specific characters you're working with rather than assuming LEN always returns 1 per visible symbol.

What happens if you use LEN on an entire range instead of one cell?

In Excel 365, =LEN(B2:B10) spills a separate character count for each cell in the range down the column. In older, non-dynamic versions of Excel, the same formula entered normally returns only the result for the first cell, unless you wrap it in SUMPRODUCT or enter it as a legacy array formula with Ctrl+Shift+Enter.

Related Functions

Function	Use this when...
TRIM	You need to remove extra spaces before measuring or comparing text length.
MID	You need to extract a specific number of characters from the middle of a string, often using LEN to calculate how many.
FIND	You need the position of a character so LEN can calculate how much text comes before or after it.