# How to count cells with extra spaces in Excel

Compare each cell's length with its trimmed length: `=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))`.

**Excel:** `=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Name |  | Cells with extra spaces |
| Lena Park |  |  |
| Omar Ruiz |  |  |
| Noor Haddad |  |  |
| Tom  Becker |  |  |
| Priya Shah |  |  |
| Ines Lopez |  |  |
| Jon Wells |  |  |
| Mia Chen |  |  |
| Raj Iyer |  |  |

Formula in D1: `=SUMPRODUCT(--(LEN(A2:A10)<>LEN(TRIM(A2:A10))))`

Result: 4

## 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.

## Other ways

- `=SUMPRODUCT(--(A2:A10<>TRIM(A2:A10)))`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-cells-with-extra-spaces