Home › Excel functions › TRIM

TRIM in Excel: Remove Extra Spaces

TRIM removes extra spaces from text: all spaces at the start and end, and double spaces between words. It's the first fix to try when a lookup returns #N/A for a value you can clearly see.

TRIM · 2 min read

TRIM syntax

One argument: the text or cell to clean. TRIM keeps single spaces between words.

Basic example

The spaces you can't see are the problem. Use =LEN(A2) to count characters: if "Mango" has a length of 7, there are two extra spaces somewhere.

Fix VLOOKUP and MATCH errors

"Mango " with a trailing space doesn't match "Mango". You can clean the lookup value inside the formula:

If the spaces are in the table itself, clean that column once (see below) instead of trimming in every formula. More on this in the VLOOKUP guide.

Replace the original data with the trimmed version

1. Add a helper column with =TRIM(A2) and copy it down.
2. Copy the helper column.
3. Select the original column and use Paste Special › Values (Ctrl+Alt+V, V, Enter).
4. Delete the helper column.

The spaces TRIM doesn't remove

Text copied from websites often contains non-breaking spaces (character 160). They look like normal spaces, but TRIM only removes the regular space (character 32). Replace them first, then trim:

For line breaks and other invisible control characters from exported data, add CLEAN:

Numbers with spaces

TRIM always returns text, even if the result looks like a number. To get a real number you can calculate with, wrap it in VALUE: =VALUE(TRIM(A2)).