LEN function in Excel

LEN returns the number of characters in a text, counting spaces.

LEN counts characters. That sounds like a curiosity, but it is one of the most useful tools for checking data: finding hidden spaces, validating the length of a code or phone number, and working out where to cut a text.

Syntax

=LEN(text)

Argument	What it is
`text`	The text, or a cell that holds it.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=LEN(C2)
Result: 10
The code PB-10-1234 has ten characters, dashes included.
=LEN(A2)
Result: 15
" asha verma " looks like two short words. LEN counts every space as well, the ones at the ends included.
=LEN(TRIM(A2))
Result: 10
After TRIM removes the extra spaces, the length is 10.
=LEN(A2)-LEN(TRIM(A2))
Result: 5
The difference is the number of unwanted spaces.
=LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1
Result: 2
A word count: remove the spaces, see how much shorter the text became, and add one.
=LEN(B2)-LEN(SUBSTITUTE(B2,".",""))
Result: 2
How many dots are in the email address.
=IF(LEN(C2)=10,"OK","Check")
Result: OK
A length check: is the code exactly ten characters?
=LEN(D2)
Result: 5
"1,250" stored as text has five characters. A real number would count only its digits.

How it works

LEN returns how many characters a cell contains. Letters, digits, punctuation and spaces all count, including spaces at the start and end that you cannot see.

For a number, LEN counts the characters of the stored value, not of the display. A cell that holds 1250 and shows "1,250.00" has a length of 4.

Finding invisible spaces

Two cells can look identical and fail to match in a lookup because one has a space at the end. Put =LEN(A2) beside them. If one says 10 and the other 11, you have found the reason. The second and third examples show the extra count, and TRIM removes it.

Checking lengths

Many identifiers have a fixed length: a ten-digit mobile number, a six-digit PIN code, a ten-character PAN. A helper column with =LEN(A2) and a filter for anything that is not the right number finds the bad entries quickly. As a Data Validation rule, =LEN(A2)=10 stops wrong lengths from being typed at all.

Remember that a number format can hide the truth. A mobile number stored as a number loses any leading zero, so its length may be right only by luck.

Counting a character or a word

There is no function that counts how often a character appears in a cell. The standard trick uses LEN twice:

=LEN(text) - LEN(SUBSTITUTE(text, character, ""))

Remove every copy of the character, and the text gets shorter by exactly the number of copies. The sixth example counts dots.

The same idea counts words: count the spaces and add one. Trim the text first, or double spaces will inflate the figure: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1.

To count a longer piece of text, divide by its length: =(LEN(A1)-LEN(SUBSTITUTE(A1,"ab","")))/LEN("ab").

Helping LEFT, RIGHT and MID

LEN supplies the total that the cutting functions need.

Everything except the first three characters: =RIGHT(A1, LEN(A1) - 3)
Everything except the last character: =LEFT(A1, LEN(A1) - 1)
The last word, the part after a marker: see LEFT and RIGHT.

Adding up lengths

=SUMPRODUCT(LEN(A2:A100)) gives the total number of characters in a range, useful when text has a size limit.

Common mistakes

Forgetting that spaces count.
Expecting LEN of a formatted number or date to match what is displayed. A date cell has the length of its serial number, usually 5.
A cell that looks empty with a LEN of 1. It holds a space.

In Google Sheets

LEN is the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.