How to / Text
How to remove extra spaces in Excel
Use =TRIM(A2). It removes the spaces at the start and the end, and leaves one space between words.
A name with spaces in the wrong places
| A | B | C | |
|---|---|---|---|
| 1 | Name | ||
| 2 | Ana Silva | Ana Silva | |
| 3 | ben LEE | ||
| 4 | CHLOE o'neil |
In C2: =TRIM(A2) → Ana Silva
A2 holds two spaces in front, three in the middle and one at the end.
How it works
- TRIM deletes every space before the first character and after the last.
- Between words, it reduces any run of spaces to one.
- The result is a new value; the original cell is unchanged.
Variations
How many characters were removed
A B C 1 Name 2 Ana Silva 5 3 ben LEE 4 CHLOE o'neil In C2:
=LEN(A2)-LEN(TRIM(A2))→ 5A quick test for hidden spaces: anything above 0 means the cell had some.
Clean the spaces and the capitals together
A B C 1 Name 2 Ana Silva Ana Silva 3 ben LEE 4 CHLOE o'neil In C2:
=PROPER(TRIM(A2))→ Ana SilvaRemove every space, including those between words
A B C 1 Name 2 Ana Silva AnaSilva 3 ben LEE 4 CHLOE o'neil In C2:
=SUBSTITUTE(A2, " ", "")→ AnaSilvaUseful for phone numbers and codes.
Watch out for
- Spaces that TRIM does not remove. Text copied from web pages often contains non-breaking spaces, which look the same but are a different character. Replace them first:
=TRIM(SUBSTITUTE(A2, CHAR(160), " ")). - Replacing the original. To keep only the clean version, copy the TRIM column, select the original, and use Paste Special > Values. Then delete the TRIM column.
- Numbers come back as text. TRIM returns text even when the cell held digits. Wrap it in VALUE to get a number:
=VALUE(TRIM(A2)).
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- TRIM: Removes extra spaces, leaving single spaces between words.
- SUBSTITUTE: Replaces specific text with new text.
- CLEAN: Removes non-printable characters.
- LEN: The number of characters in a text value.
- PROPER: Capitalises The First Letter Of Each Word.
- VALUE: Converts text that looks like a number into a number.