How to Remove Extra Spaces in Excel (Even When TRIM Doesn't Work)

By The NativeOffice Team

To remove extra spaces in Excel, put =TRIM(A2) in an empty column, fill it down, then copy the results and paste them back as values. TRIM deletes spaces at the start and end of a cell and shrinks runs of spaces between words to a single space. If TRIM seems to do nothing, the culprit is almost always a non-breaking space (character 160) copied from a web page or PDF; use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) instead.

Stray spaces are one of the most common reasons a VLOOKUP returns #N/A, a Remove Duplicates pass misses obvious duplicates, or two names that look identical refuse to match. Below are the methods that work, in the order you should try them.

How do I remove extra spaces with the TRIM function?

TRIM is the standard fix and works in Excel, Google Sheets, LibreOffice Calc and NativeOffice Sheets.

Insert an empty column next to the messy data (for example, column B next to column A).
In B2, type =TRIM(A2) and press Enter.
Double-click the fill handle (the small square at the bottom-right of the cell) to fill the formula down the column.
Select the cleaned column, copy it, then use Paste Special, Values to paste it over the original column.
Delete the helper column.

Step 4 matters. Until you paste as values, the clean text is only a formula result, and deleting the original column will break it.

Why is TRIM not removing the spaces?

If =LEN(A2) and =LEN(TRIM(A2)) return the same number but you can still see a space, the cell contains a character TRIM was never designed to remove. There are three usual suspects.

Non-breaking spaces (CHAR 160)

Web pages use a non-breaking space to stop words from wrapping apart. It looks exactly like a normal space, but Excel's TRIM only removes the ordinary space (character 32). Swap the non-breaking spaces for normal ones first, then trim:

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

If CHAR(160) doesn't work in your version, use UNICHAR(160) in its place.

Line breaks and other invisible characters

Data exported from other systems can carry line breaks, tabs or control characters. CLEAN removes most of them, so the all-in-one formula is:

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

The cell is formatted as Text

If the formula itself shows up in the cell instead of a result, the column is formatted as Text. Change the format to General, then click into the cell and press Enter again.

How do I find out which invisible character is in a cell?

Find the position of the suspicious character. If it is the first character, use =CODE(LEFT(A2,1)); for the last, =CODE(RIGHT(A2,1)).
A result of 32 is a normal space, 160 is a non-breaking space, 10 is a line break and 9 is a tab.
For characters outside the basic set, use UNICODE instead of CODE, then remove that character with =SUBSTITUTE(A2,UNICHAR(code),"").

How do I remove spaces without a formula?

Find and Replace is faster when you want to change the cells in place.

Select the range you want to clean.
Press Ctrl+H to open Find and Replace.
To remove every space (useful for phone numbers, IDs and postcodes), type one space in Find what, leave Replace with empty, and click Replace All.
To remove non-breaking spaces, click in Find what, hold Alt and type 0160 on the numeric keypad, leave Replace with empty, and click Replace All.
To collapse double spaces between words, put two spaces in Find what and one space in Replace with, then click Replace All repeatedly until Excel reports zero replacements.

Be careful with step 3 on names or sentences: it joins every word together.

How do I remove all spaces, not just the extra ones?

Use =SUBSTITUTE(A2," ","") to strip every space from a cell. This is the right choice for values that should never contain spaces, such as account numbers or SKUs, and the wrong one for names and addresses, where TRIM is what you want.

How do I clean spaces in a whole sheet at once?

Helper columns get tedious when a workbook has dozens of text columns. Two options handle a whole range in one pass:

Power Query in Excel: load the table, select the columns, then use Transform, Format, Trim (add Clean as well for invisible characters) and load the result back to the sheet.
NativeOffice Sheets: select the range and run the Data Cleanser from the Data tab, which trims whitespace along with duplicates and inconsistent dates. It runs locally on your computer, so the file is never uploaded, and it works offline once you've signed in.

Whichever tool you use, work on a copy of the sheet first, and trim before you remove duplicates or run lookups, so values that differ only by a hidden space are treated as the same.

FAQ

Does TRIM remove spaces between words?

Only the extra ones. TRIM keeps a single space between words and removes the rest, plus every leading and trailing space.

Why does VLOOKUP return #N/A when the values look the same?

Usually because one side has a trailing space or a non-breaking space. Wrap the lookup value in TRIM, or clean both columns with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) before matching.

Can I remove extra spaces in Google Sheets the same way?

Yes. TRIM, SUBSTITUTE and CLEAN work the same way, and Google Sheets also has Data, Data cleanup, Trim whitespace for a selected range.

Will trimming change my numbers?

Numbers stored as text with stray spaces become clean text after TRIM. Multiply the result by 1, or use =VALUE(TRIM(A2)), if you need them as real numbers again.

Can I remove spaces from a spreadsheet without uploading it to a website?

Yes. Excel, LibreOffice Calc and NativeOffice Sheets all clean data on your own computer, so there's no need to paste sensitive lists into an online tool. You can get NativeOffice for Windows at https://nativeoffice.online/features.