Count Characters Without Spaces

All versionsLEN

LEN counts spaces too. To count just the visible characters — for character limits, density, or validation — subtract the spaces with a LEN/SUBSTITUTE pair.

Quick formula: characters in A2 ignoring spaces:

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

Remove all spaces, then measure the length — the non-space character count.

Functions used (tap for the full reference guide):

LEN SUBSTITUTE

The example

Total vs non-space characters.

	A	B
1	Text	No-space count
2	a b c d	4

The formula

Length after removing spaces:

=LEN(SUBSTITUTE(A2, " ", "")) // "a b c d" → 4

How it works

Strip spaces, then count:

SUBSTITUTE(A2, " ", "") removes every space.
LEN(…) measures the result — the count of non-space characters.
To count spaces instead, subtract: LEN(A2) − LEN(SUBSTITUTE(A2," ","")).
That space count plus one is also the rough word count for single-spaced text.

All whitespace, not just spaces? Tabs and non-breaking spaces (CHAR(9), CHAR(160)) aren’t the regular space — SUBSTITUTE those too, or TRIM/CLEAN first, for a true visible-character count.

Try it: interactive demo

Live demo

Type text.

With spaces · Without

Variations

Count spaces

How many spaces:

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

Word count

Spaces + 1:

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

All whitespace

Also tabs/nbsp:

=LEN(SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),""))

Pitfalls & errors

Only regular spaces. CHAR(160) (non-breaking) and CHAR(9) (tab) survive — substitute them too for a true count.

Empty cell. Returns 0, which is correct.

Word count needs TRIM. Double spaces inflate the spaces-plus-one word count — TRIM first.

Practice workbook

📊

Download the free Count Characters Without Spaces practice workbook

A no-space counter with the count-spaces, word-count, and whitespace variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count characters without spaces in Excel?

Use =LEN(SUBSTITUTE(A2, " ", "")) — it removes spaces, then measures the length.

How do I count the number of spaces?

Subtract: =LEN(A2)-LEN(SUBSTITUTE(A2," ","")).

Why doesn't it catch tabs or non-breaking spaces?

Those are different characters (CHAR(9), CHAR(160)). Substitute them as well, or TRIM/CLEAN first, for a true visible-character count.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Count words · Remove extra spaces · Highlight by text length

Function references: LEN · SUBSTITUTE