Count Characters in Excel: LEN With and Without Spaces
Blog / SPREADSHEETS
To count characters in Excel, use =LEN(A2). To exclude ordinary spaces, use =LEN(SUBSTITUTE(A2," ","")). LEN includes spaces by default; SUBSTITUTE removes the specified space characters before the remaining text is measured.
Measure a cell with LEN
Place the formula in a different cell from the text being measured. For example, keep a product label in A2 and put its character count in B2.
The sample 'Cedar desk' returns 10, including its internal space. Trailing spaces also contribute to the count, so text that looks identical can produce different results.
=LEN(A2)Exclude ordinary spaces with SUBSTITUTE
Nest SUBSTITUTE inside LEN to replace every ordinary space with an empty string. This changes only the text passed to the calculation; it does not overwrite the source cell.
For 'Cedar desk', the result becomes 9. This is a character count without ordinary spaces, not a count without every possible whitespace or invisible character.
=LEN(SUBSTITUTE(A2," ",""))Inspect unexpected differences
Imported text can contain tabs, line breaks, or nonbreaking spaces that do not match the ordinary space used in the formula. Decide which separators your task requires you to exclude before adding more substitutions.
For a mailing-label audit, a line break between the street and town may need to remain in the stored text. Do not clean the original data merely to make its count resemble a single-line label.
Account for Unicode and workbook behavior
Microsoft documents improved surrogate-pair handling in workbooks using Compatibility Version 2: LEN counts a surrogate pair as one character, while variation selectors remain separate. Consequently, emoji counts need not match a visual symbol count.
For plain labels, a helper column is a practical way to compare lengths across rows. For a destination with a strict submission limit, check a representative pasted value in that destination as well as in Excel.
Frequently asked questions
Does LEN include spaces?
Yes. LEN counts spaces, including spaces after the visible text.
How do I count characters without spaces?
Use =LEN(SUBSTITUTE(A2," ","")). This excludes ordinary spaces only.
Why can emoji counts differ between tools?
Tools may measure different Unicode units. Microsoft also documents workbook compatibility differences in LEN's surrogate-pair handling.