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. The wider set of guards — Match entire cell contents, the Within box, and the wildcards that widen a search further than intended — are covered in the full Find and Replace walkthrough. A name column usually needs the opposite treatment anyway — separating names in Excel splits it on the space instead of deleting it. 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.

Make the TRIM result permanent (and get your numbers back)

TRIM does not change the column you were trying to fix — it produces a second column of results next to it, and two things usually go wrong next.

The first is that deleting the original column empties the new one. The formulas were pointing at it, so removing it leaves a column of #REF! errors. The fix is to convert the results to plain text before deleting anything: select the TRIM column, copy it, then right-click the same cells and use Paste Special. Microsoft's instruction for the option is exactly this — to paste values only, select Values. The cells now hold trimmed text with no dependency on the source, and the original column can go.

The second problem is quieter and costs more. TRIM returns text — always, regardless of what the value looks like. A quantity that arrived as 1 250 comes out as the text 1250, and SUM skips it silently, so a total over 5,000 rows is short by however many of those slipped through. Excel usually marks the cells with a green corner triangle and a "Number Stored as Text" note, which is worth trusting. To convert as you clean, wrap the formula: =VALUE(TRIM(A2)). Microsoft describes VALUE as converting "a text string that represents a number to a number", which is precisely the step TRIM leaves undone.

A short order of operations avoids both: trim into a helper column, wrap in VALUE where the column is numeric, paste back as values, delete the helper. Get the column into one consistent state first, because everything downstream — sorting, matching, removing duplicates, a lookup against another sheet — compares the stored value rather than the one you see. Two entries that look identical will not match while one is text and the other is a number.

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. Stray spaces often show up because two fields were glued together by hand; rebuilding that field with the column combiner picks the separator once and applies it to every row, so no row ends up with two spaces and the next with none. Phone numbers deserve their own step rather than a blanket space-strip — formatting a phone column to E.164 removes the separators and adds the missing country code in one operation.

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. If the file is still a raw export rather than a workbook, you can skip the spreadsheet and remove duplicate rows from the CSV with the ignore-spacing option switched on, which handles the trim and the de-dupe in the same pass. 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.