How to Remove Spaces in Excel (5 Reliable Ways)

Knowing how to remove spaces in Excel means clearing the leading, trailing, extra, and hidden characters that stop VLOOKUP matches, break sorting, and inflate totals. Unlike a manual cell-by-cell edit, the right function or one Find & Replace pass cleans an entire column in seconds — and does it without changing the real data.

Why extra spaces break your spreadsheet

Stray spaces are invisible, but they change how Excel reads every value. A cell that shows Paris might actually hold Paris with a trailing space, and to Excel those are two different values. That single character causes lookups to fail, duplicate rows to slip past a de-dupe, SUMIF to skip records, and a sorted column to scatter near-identical entries. Data pasted from websites, PDFs, and other apps is the usual culprit, because it carries spacing the original layout used for alignment.

A quick way to confirm a hidden space is the LEN function. Put =LEN(A2) next to a suspect cell and compare the count to what you can see: if Paris returns 6 instead of 5, there is a trailing space you cannot see. You can also click into the cell and press the End key to land the cursor after any invisible characters. Both checks take a second and tell you which fix to reach for.

The three problems you will hit most are leading and trailing spaces (before or after the text), extra spaces between words (two or more where one belongs), and non-breaking spaces that look normal but behave differently. Each has its own fix, so the first step is knowing which one you have. If a value looks clean but still won't match, assume a hidden space and test it before you rewrite anything. Getting spacing right is often the first move in a broader spreadsheet cleanup routine that also handles blank rows, casing, and stray punctuation.

Remove leading, trailing, and extra spaces with TRIM

The TRIM function is the default fix because it targets the common cases without destroying the spaces you want to keep. Per Microsoft, TRIM "removes all spaces from text except for single spaces between words" (Microsoft, TRIM function). So it clears the front, clears the back, and collapses any run of internal spaces down to one — which is exactly what you want for names, cities, and product labels.

Use it like this:

1. Click an empty helper cell next to your data, for example B2.
2. Enter =TRIM(A2).
3. Press Enter. John Smith becomes John Smith.
4. Drag the fill handle down to apply it to the whole column.
5. Select the cleaned cells, copy them, then use Paste Special > Values over the original column so you can delete the helper.

Two limits are worth remembering. TRIM only removes the standard ASCII space (character 32), so it will not touch the non-breaking spaces covered below. And because TRIM returns text, a column of numbers run through TRIM comes back as text — wrap it in VALUE or multiply by 1 if you need it numeric again. For most name and address columns, though, TRIM alone fixes the extra-space problem and leaves the words readable.

Delete every space with Find & Replace

When you want to strip every space, including the ones between words, Find & Replace is faster than any formula because it edits the cells in place. This is the tool for postal codes, phone numbers, product SKUs, and any value that should contain no spaces at all.

1. Select the column or range you want to clean.
2. Press Ctrl+H to open Replace, or go to Home > Editing > Find & Select > Replace (Microsoft, Find or replace text and numbers).
3. In the Find what box, press the spacebar once.
4. Leave the Replace with box completely empty.
5. Click Replace All. Excel reports how many spaces it removed.

Because Replace All acts on the selection, restrict your range first so you do not wipe spaces from columns that need them, such as full-name or address fields. If you prefer a formula that leaves the original intact, SUBSTITUTE does the same job: =SUBSTITUTE(A2," ","") replaces new text for old text in a string and, with an empty replacement, deletes all spaces (Microsoft, SUBSTITUTE function). Find & Replace wins for speed on one column; SUBSTITUTE wins when you want a repeatable formula you can audit later.

Fix the spaces TRIM can't touch (non-breaking spaces)

If TRIM ran and spaces are still there, you are dealing with non-breaking spaces — character 160 — and they need a different fix. Web pages use the non-breaking space (  in HTML) to keep words together, and it survives a copy-paste into Excel looking identical to a normal space. TRIM ignores it, so the value stays broken and your lookups keep failing.

There are two reliable ways to clear it:

• Formula: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). The inner SUBSTITUTE converts every non-breaking space (CHAR(160)) into a regular space, then TRIM tidies the result. To remove them entirely rather than convert, use =SUBSTITUTE(A2,CHAR(160),"").
• Find & Replace: open Replace, then in the Find what box enter the non-breaking space by holding Alt and typing 0160 on the numeric keypad. Leave Replace with empty and click Replace All.

The CLEAN function is a related tool, but read its scope before you rely on it: Microsoft notes CLEAN "was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31)" and that "by itself, the CLEAN function does not remove these additional nonprinting characters" in the higher Unicode range (Microsoft, CLEAN function). Because 160 sits outside that 0–31 range, CLEAN will not remove a non-breaking space — use the SUBSTITUTE approach for that, and save CLEAN for line breaks and other invisible control characters pasted in from other systems.

Remove spaces in a CSV without uploading it

If your data lives in a CSV rather than a workbook, you can strip spaces without opening Excel at all — and without handing the file to a server. Opening a CSV in Excel to run TRIM risks a second problem: Excel may reformat long numbers, drop leading zeros from postal codes, or mangle dates on the way in and out. When the source is a plain CSV headed for a CRM or another import, it is cleaner to fix the spaces in the file directly.

That is where a browser tool fits. Sigmera's find-and-replace tool for CSV files lets you target a column, replace spaces with nothing (or a non-breaking space with a normal one), and export a corrected file — all in the browser. Nothing uploads to a server, so the data never leaves your device and stays GDPR-safe by default, which matters when the column holds customer names, emails, or phone numbers. You can pair it with the other in-browser cleaning tools to fix casing or delimiters in the same pass.

Space cleanup rarely travels alone. A messy export usually also needs duplicate rows removed and sometimes two columns joined into one — for example stitching a trimmed first and last name back together. Do the spaces first: once every value is trimmed the same way, de-duplication and matching become reliable, and the rest of the cleanup falls into place. Whether you fix it with TRIM, Find & Replace, or a browser tool, the goal is the same — values that match, sort, and total the way you expect.