Home › Excel functions › LEN

LEN Function in Excel: Count Characters and Words

LEN returns the number of characters in a cell, including spaces. Simple on its own, but it's the key to spotting invisible spaces, validating codes and counting words.

LEN · 2 min read

In this guide

LEN syntax
Find hidden spaces
Check the length
Count words
Count a character
With LEFT and RIGHT
FAQ

LEN syntax

=LEN(text)

Formula	Result	Why
`=LEN("Excel")`	5	5 letters
`=LEN("Anna Jansen")`	11	The space counts too
`=LEN("Mango ")`	6	Trailing space
`=LEN(A2)` with 1.50 shown, 1.5 stored	3	LEN counts the value, not the formatting

Find hidden spaces

When a lookup returns #N/A for a value that looks correct, compare its length with the trimmed version:

=LEN(A2) <> LEN(TRIM(A2))

TRUE means there are extra spaces. Clean them with TRIM.

Check codes have the right length

Postal codes, IBANs and product codes usually have a fixed length. Flag the wrong ones:

=IF(LEN(A2)<>6, "Check", "")

You can also enforce it on input: Data › Data Validation › Allow: Text length › equal to 6.

Count words in a cell

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

The number of spaces plus one is the number of words. "The quick brown fox" returns 4. TRIM makes sure double spaces don't inflate the count. For an empty cell this returns 1; wrap it if needed: =IF(A2="", 0, …).

Count a specific character

=LEN(A2) - LEN(SUBSTITUTE(A2, "@", ""))

How many @ signs are in the cell. Useful to check if a cell contains more than one email address. See SUBSTITUTE.

With LEFT, RIGHT and MID

LEN tells you how much text there is, which helps when removing characters:

Remove the first 3 characters: =RIGHT(A2, LEN(A2)-3)
Remove the last 2 characters: =LEFT(A2, LEN(A2)-2)

Frequently asked questions

Does LEN count spaces?

Yes, every space counts as a character, including leading and trailing spaces.

Can LEN count characters in a range?

Yes, with SUM: =SUM(LEN(A2:A10)) in Microsoft 365, or =SUMPRODUCT(LEN(A2:A10)) in older versions.

Why does LEN return a different number than I see?

The cell contains hidden characters (spaces, line breaks) or the displayed number is formatted differently from the stored value.