← Error directory

How to Clean Extra Spaces in Excel

Extra spaces break lookups and pivots. Non-breaking spaces (CHAR 160) from web paste are worse because TRIM alone does not remove them.

TRIM and CLEAN

=TRIM(A2)
=CLEAN(A2)
=TRIM(CLEAN(A2))

Non-breaking spaces

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

Bulk cleanup

1. Helper column with the formula above
2. Copy → Paste Special → Values
3. Or Power Query: Transform → Format → Trim / Clean

Fix lookups after cleanup

=XLOOKUP(
  TRIM(SUBSTITUTE(E2,CHAR(160)," ")),
  TRIM(SUBSTITUTE(A2:A1000,CHAR(160)," ")),
  B2:B1000,
  "Not found"
)

VLOOKUP #N/A hidden characters · Error directory