How to Remove Spaces in Excel (Extra, Leading, All)

To remove spaces in Excel, use TRIM for extra spaces and SUBSTITUTE for all of them. TRIM cleans up spaces at the start and end of a cell and turns double spaces into single ones, which fixes most imported data. This guide shows when to use each, how to deal with stubborn spaces from websites, and how to find hidden spaces before they break your formulas.

Extra spaces matter because Excel sees "Maria Lopez " and "Maria Lopez" as different values. That one hidden space can make a VLOOKUP return #N/A or a COUNTIF return 0.

Which method do you need?#

Pick the method based on which spaces you want gone:

How to remove extra spaces with TRIM#

TRIM removes every space except single spaces between words. It takes just one argument:

=TRIM(A2)

With " Maria Lopez " in A2, the result is Maria Lopez. The two leading spaces, the extra two in the middle and the trailing space are all gone. Here's how to clean a whole column:

1. In B2, type =TRIM(A2) and press Enter.
2. Fill it down to the last row (double-click the fill handle).
3. Copy column B, then paste it over column A as values: Paste Special > Values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V).
4. Delete column B.

TRIM works in every version of Excel. The TRIM function guide has a quick reference.

How to remove all spaces with SUBSTITUTE#

When no spaces should be left at all, such as in phone numbers, IDs or postcodes, replace every space with nothing:

=SUBSTITUTE(A2," ","")

The second argument is a space in quotes, and the third is two quotes with nothing between them. "AB12 3CD" becomes AB123CD. SUBSTITUTE swaps every copy of the text you name, so it is also the tool for dashes, dots and other characters. The guide to removing characters in Excel covers those, and the SUBSTITUTE function guide shows the optional fourth argument.

If the cleaned value is a number, such as "1 250" with a space as a thousands separator, wrap it in VALUE so you can do maths with it:

=VALUE(SUBSTITUTE(A2," ",""))

The result is the number 1250.

Why TRIM doesn't remove some spaces#

If TRIM leaves spaces behind, they are probably non-breaking spaces. Web pages use them to keep words together, and they come along when you copy and paste from a browser. To Excel, a non-breaking space is character 160, not the normal space, character 32. TRIM only removes character 32.

Turn the non-breaking spaces into normal ones first, then trim:

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

CHAR(160) produces the non-breaking space, SUBSTITUTE changes each one to a normal space, and TRIM cleans up. If the data also has line breaks or other invisible characters, add CLEAN: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). CLEAN removes non-printing characters such as line breaks (character 10).

How to find cells with hidden spaces#

Spaces at the end of a cell are invisible, so check with LEN, which counts every character, including spaces:

=LEN(A2)<>LEN(TRIM(A2))

This returns TRUE when the cell has extra spaces. For "Tom Becker", LEN gives 11 and LEN of the trimmed text gives 10, so the result is TRUE. Fill it down a column, then filter for TRUE to see every messy row.

To count them all at once, use =SUMPRODUCT(--(LEN(A2:A100)<>LEN(TRIM(A2:A100)))).

Remove spaces with Find and Replace#

Find and Replace removes spaces without a helper column, which is handy for a quick fix. To remove every space in a range:

1. Select the cells.
2. Press Ctrl+H (Mac: ⌃H) to open Find and Replace.
3. In Find what, type a single space. Leave Replace with empty.
4. Click Replace All.

This removes all spaces, including the ones between words, so it suits codes and numbers rather than names. To fix double spaces only, find two spaces and replace with one, then click Replace All again until Excel finds nothing. See the guide to Find and Replace in Excel for more options.

Remove spaces with Power Query#

Power Query is useful when you clean the same kind of file again and again. Select the data, choose Data > From Table/Range, right-click the column header and pick Transform > Trim. Click Close & Load to send the result back to a sheet, and next time just click Refresh.

Be aware that Power Query's Trim only removes spaces at the start and end. Unlike the TRIM function, it leaves double spaces between words. To fix those too, use Replace Values in Power Query to swap two spaces for one, or clean the column with the TRIM function instead.

One more thing to know: TRIM and SUBSTITUTE always return text. If you trim a column of numbers, wrap the formula in VALUE so the results still add up.

Spaces and other clean-up tools#

Spaces are often only one part of a messy column. If names and surnames are in one cell separated by spaces, Text to Columns can split them, but trim first, or double spaces create empty columns. For the full routine of trimming, fixing case, removing duplicates and converting text numbers, read the guide to cleaning messy data. And for swapping one piece of text for another, see SUBSTITUTE and REPLACE.

Practise it#

Hidden spaces are one of the most common reasons formulas fail in real jobs and in tests. Practise cleaning text in a live spreadsheet: you type TRIM, SUBSTITUTE and CLEAN formulas, and it checks each result instantly. Then try the basic Excel test to see how quickly you can spot and fix dirty data.