LEN Function in Excel: Count Characters in a Cell
Use LEN to count characters in Excel, find hidden spaces, check code and ID lengths, count words, and limit text for forms, SMS and social posts.
By Ayoub Farid · · 1 min read
LEN returns the number of characters in a cell, including spaces. It's small, but it's the go-to tool for spotting hidden spaces and checking that codes have the right length.
Syntax
=LEN(text)=LEN("Latte") → 5. =LEN("Latte ") → 6.
Find hidden spaces
=LEN(A2)<>LEN(TRIM(A2))TRUE means the cell has extra spaces. These break lookups and counts. See TRIM.
Check ID lengths
Flag phone numbers or codes that aren't 10 characters:
=IF(LEN(A2)<>10,"Check","")Or use Data Validation > Text length to stop wrong lengths being entered.
Count words
=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)Counts spaces between words, plus one.
Count a specific character
How many commas:
=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))Character limits
Remaining characters for a 160-character message:
=160-LEN(A2)With LEFT, RIGHT, MID
LEN often supplies the length in text formulas, e.g. everything after the first space: =RIGHT(A2,LEN(A2)-FIND(" ",A2)). See LEFT, RIGHT and MID.
Numbers
LEN works on the value, not the format: =LEN(1234.5) is 6 even if the cell displays "$1,234.50".
Frequently asked questions
How do I count characters in Excel?
=LEN(A2).
Does LEN count spaces?
Yes, including leading and trailing spaces.
How do I count words in a cell?
Count spaces with LEN and SUBSTITUTE and add one, as shown above.
Does LEN work in Google Sheets?
Yes.