# How to find cells with leading or trailing spaces in Excel

Check the first and last characters: =OR(LEFT(A2)=" ",RIGHT(A2)=" ") returns TRUE for cells that start or end with a space.

**Excel:** `=OR(LEFT(A2)=" ",RIGHT(A2)=" ")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Customer | Space at either end? |
| Northwind |  |
| Contoso |  |
| Fabrikam |  |
| Litware |  |
| Adatum  Corp |  |
| Proseware |  |

Formula in B2, filled to B7: `=OR(LEFT(A2)=" ",RIGHT(A2)=" ")`

Result: FALSE; TRUE; TRUE; FALSE; FALSE; FALSE

## How it works

LEFT and RIGHT with no length return one character, the first and the last. If either is a space, the cell has a leading or trailing space, the kind that makes "Contoso " fail to match "Contoso" in a lookup.

## Other ways

- `=A2<>TRIM(A2)`: Catches any extra space, including doubled spaces inside the text. ("Adatum  Corp", with two spaces in the middle, is TRUE too.)

## Common mistakes

- Non-breaking spaces (character 160) aren't " ", so these tests miss them. Add `SUBSTITUTE(A2,CHAR(160)," ")` inside.
- An empty cell returns FALSE, which is what you want; a cell with only a space returns TRUE.

## Questions

**How do I count the cells with extra spaces?**

Use `=SUMPRODUCT(--(A2:A7<>TRIM(A2:A7)))`.

**How do I highlight them?**

Use the test as a conditional formatting rule: `=OR(LEFT(A2)=" ",RIGHT(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/find-cells-with-leading-or-trailing-spaces