How to count cells with extra spaces in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))Compare each cell's length with its trimmed length: =SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10)))).
- Result on the example
- 4 in
D1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D1. Change any input and the result updates, or edit the formula to experiment.
How it works
TRIM removes spaces at the start and end and shrinks doubled spaces inside the text to one. If a name gets shorter when trimmed, it had extra spaces. Four names do: a trailing space, a leading space, a double space in the middle, and two trailing spaces.
=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))Result4
A2:A109 rows × 1 col: "Lena Park", "Omar Ruiz ", " Noor Haddad", "Tom Becker", …A range: 9 cells in one column.
LEN(A2:A10){9 rows × 1 col} 9, 10, 12, 11, …LEN: Number of characters.- text
A2:A109 rows × 1 col: "Lena Park", "Omar Ruiz ", " Noor Haddad", "Tom Becker", …
- text
TRIM(A2:A10){9 rows × 1 col} "Lena Park", "Omar Ruiz", "Noor Haddad", "Tom Becker", …TRIM: Removes extra spaces, leaving single spaces between words.- text
A2:A109 rows × 1 col: "Lena Park", "Omar Ruiz ", " Noor Haddad", "Tom Becker", …
- text
LEN(TRIM(A2:A10)){9 rows × 1 col} 9, 9, 11, 10, …LEN: Number of characters.- text
TRIM(A2:A10){9 rows × 1 col} "Lena Park", "Omar Ruiz", "Noor Haddad", "Tom Becker", …
- text
LEN(A2:A10)<>LEN(TRIM(A2:A10)){9 rows × 1 col} FALSE, TRUE, TRUE, TRUE, …The <> operator tests whether they differ.
-(LEN(A2:A10)<>LEN(TRIM(A2:A10)){9 rows × 1 col} 0, -1, -1, -1, …Makes the value negative.
--(LEN(A2:A10)<>LEN(TRIM(A2:A10)){9 rows × 1 col} 0, 1, 1, 1, …Makes the value negative.
SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(LEN(A2:A10)<>LEN(TRIM(A2:A10)){9 rows × 1 col} 0, 1, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10)))). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(A2:A10<>TRIM(A2:A10))) | 4 | Compares the text with its trimmed version directly; the same count. |
Common mistakes
- Non-breaking spaces (CHAR(160)) from web pages aren't removed by TRIM, so those cells look clean to this formula. Check with
=SUMPRODUCT(--ISNUMBER(FIND(CHAR(160),A2:A10))). - Google Sheets' TRIM also removes extra spaces, so the formula works there unchanged.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count cells with extra spaces
D1, count the names in A2:A10 that have leading, trailing or doubled spaces.Questions
How do I remove the spaces once I find them?
Use =TRIM(A2) in a helper column, then copy and paste as values over the original.
How do I highlight the cells with extra spaces?
Use conditional formatting with the formula =LEN(A2)<>LEN(TRIM(A2)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.