Excel Remove Space — Complete Guide (2026)

Excel Remove Space — Every Method That Actually Works

Imported a CSV. Pasted from a website. Copied from a PDF. Suddenly your VLOOKUPs are returning #N/A and your sort order is wrong. The culprit? Invisible spaces clinging to your data like lint.

Here's the thing: "space" in Excel isn't one thing. There's the regular space (ASCII 32), the non-breaking space (CHAR 160) that web pages love, the tab character, line breaks, and a handful of weird Unicode whitespace characters that look identical but trip up every formula you throw at them. That's why a single fix rarely solves a real cleanup job.

This guide walks through the five methods that handle 99% of real-world cleanup. You'll see when to reach for the excel trim function, when TRIM lets you down, and how to chain SUBSTITUTE with CHAR(160) to handle the worst offenders. Each method comes with the exact formula, the keyboard shortcut, and a note on when not to use it.

The Five Methods Covered

Method 1 is TRIM — the function 80% of Excel users reach for first. It handles leading, trailing, and extra internal spaces in one shot. Method 2 is excel substitute function, which removes all spaces including the ones inside words (useful for stripping spaces from product codes or phone numbers). Method 3 is Find & Replace with Ctrl+H — no formulas, instant result, works on any cell range you select.

Method 4 is the non-breaking space fix. This is the one nobody tells you about until you've already lost two hours to it. Method 5 is Power Query's Trim and Clean transformations — overkill for a single cell, but the right tool when you're cleaning thousands of rows on a recurring schedule. We'll also cover the CLEAN function for non-printable characters and how to find and replace in excel when you need to strip line breaks (CHAR 10 and CHAR 13) from cells.

Short answer for the impatient: try TRIM first. If it doesn't work, you have NBSP. If you want every space gone, use SUBSTITUTE. If you're cleaning a recurring CSV import, build it in Power Query.

Method 1: TRIM — The Default Tool for Most Jobs

TRIM is the workhorse. Type =TRIM(A1) in any blank cell, press Enter, and Excel returns the text from A1 with all leading and trailing spaces stripped and any sequence of internal spaces collapsed down to one. So " John Smith " becomes "John Smith". Three words, two spaces between them, none at the start or end.

That's it. That's the whole function.

The catch? TRIM only recognizes the standard space character — ASCII 32, the one you get when you hit the spacebar. It does not touch CHAR(160), the non-breaking space that lives in HTML, PDFs, and most word processors. If you ever run TRIM on web-scraped data and find spaces still hanging around, you've met your first real Excel boss fight. We solve it in Method 4.

The Three-Step TRIM Workflow

Step one: insert a helper column next to your messy data. Step two: enter =TRIM(A2) and double-click the fill handle to copy it down. Step three: copy the helper column, then paste it back over the original as Values (Ctrl+Alt+V, then V). Delete the helper. Done. Three minutes for a thousand rows, two minutes for a million if you have a decent machine.

Quick warning: TRIM converts numbers stored as text into text strings. If your data started as text-formatted numbers, they'll still be text after TRIM. Wrap in VALUE() if you need them numeric: =VALUE(TRIM(A1)). Worth knowing before your SUM formula starts returning zero. The reverse trap: if your column has mixed types, VALUE will error on genuinely text values like "N/A" or "Pending". Use IFERROR to guard: =IFERROR(VALUE(TRIM(A1)),TRIM(A1)).

When TRIM Actually Hurts You

TRIM is greedy in a way that occasionally matters. Internal whitespace patterns like "3 to 5 days" become "3 to 5 days" — which is usually what you want. But if you're parsing structured text where the multiple spaces signaled hierarchy (like fixed-width column data scraped from a terminal screen), TRIM destroys the structure. In those cases, use FIND or MID with specific positions instead. Know the data before you reach for the tool.

For deep-dive examples and edge cases — like when TRIM behaves differently inside an array formula — see the dedicated trim function in excel walkthrough.

Method 2: SUBSTITUTE — Remove Every Single Space

TRIM leaves single spaces between words. SUBSTITUTE doesn't care — it can wipe every space character from a string, leaving zero gaps. The syntax: =SUBSTITUTE(A1," ",""). Three arguments: the cell, the character to find (a space inside quotes), the character to replace it with (empty string, which means delete).

Use this when you need excel formulas that produce clean tokens — product codes, hash strings, IBAN numbers, anything where the space was a formatting choice but not data. Phone number "555 867 5309" becomes "5558675309". Address "123 Main St" becomes "123MainSt" — which is probably not what you want for addresses. Match the tool to the goal.

SUBSTITUTE for Specific Spaces Only

SUBSTITUTE has a fourth optional argument: which instance to replace. =SUBSTITUTE(A1," ","",1) removes only the first space. =SUBSTITUTE(A1," ","",2) removes only the second. Useful when you want to keep ONE space but strip the rest — like converting "John Q. Public" to "John Q. Public" without using TRIM (TRIM would also strip the periods' spacing in some weird locale settings).

For a deeper look at chaining SUBSTITUTE with nested calls, check the substitute formula in excel guide. SUBSTITUTE is also the building block for removing line breaks, dashes, and any other character you want gone.

Method 3: Find & Replace — No Formula Required

The fastest method for one-time cleanup: select your range, press Ctrl + H, type a single space in the "Find what" box, leave "Replace with" empty, click Replace All. Boom. Every space in the selection is gone.

Want to keep words separated? Use two spaces in the Find box, one space in Replace — running it a few times until Excel reports "0 replacements made" guarantees no double or triple spaces remain. Quick and dirty, but it works.

Worth knowing: Find & Replace operates on the cell's display value, not the underlying formula. If A1 contains =B1&" "&C1, Find & Replace won't strip that hardcoded space — you'd need to edit the formula directly.

Limiting Find & Replace to a Selection

One trap users hit: running Ctrl+H with no cells selected scans the entire active worksheet. Replace All on "every space" across an unselected sheet rips through headers, formulas, helper text — everything. Always select your target range first. Better: select the column header (click the letter), then Ctrl+H, then Replace All. The dialog only touches what's selected.

The dialog also has an Options arrow worth knowing about. Click it and you can scope replacements to the active workbook instead of just the sheet, toggle case sensitivity, or restrict matching to whole cells only. "Match entire cell contents" is a lifesaver when you're trying to delete cells that are literally just " " (two spaces) without touching cells that contain a regular sentence with spaces in it.

Method 4: The Non-Breaking Space Fix (When TRIM Fails)

You ran TRIM. Spaces are still there. You're losing your mind. Here's what's happening: the data has CHAR(160) instead of CHAR(32). Both look like a space on screen. Both take up one character width. But TRIM only knows about CHAR(32).

CHAR(160) is the non-breaking space. It's everywhere on the web — HTML uses it to prevent line breaks between words. When you copy from a browser, Word document, or PDF, you often get NBSP characters instead of regular spaces. Excel doesn't flag them. Your VLOOKUP fails. Your sort order looks wrong. TRIM does nothing.

The Two-Step NBSP Cleanup

The fix is a nested formula: =TRIM(SUBSTITUTE(A1,CHAR(160)," ")). Read it inside-out. SUBSTITUTE swaps every CHAR(160) for a regular CHAR(32) space. Then TRIM does its normal job on the now-uniform spaces. The result is clean text. This single formula handles about 95% of "why won't TRIM work" situations.

To verify NBSP is actually the problem, drop a diagnostic formula: =CODE(MID(A1,1,1)) returns the character code of the first character in A1. If it's 160, you have NBSP. If it's 32, you have a regular space. If it's something else weird like 9 (tab) or 10 (line feed), you've got an even messier dataset and need to substitute those out too: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," "),CHAR(9)," "),CHAR(10)," ")). Ugly but effective.

Don't Forget CLEAN for Non-Printable Characters

CLEAN is TRIM's lesser-known sibling. It removes ALL non-printable characters from text — that's CHAR(1) through CHAR(31), which includes tabs, line breaks, and form feeds. Web-scraped data often has these mixed in invisibly. The full one-two punch: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))). SUBSTITUTE handles NBSP, CLEAN strips the non-printables, TRIM tidies the result.

Method 5: Power Query — When You're Cleaning Thousands of Rows

Formulas work for small jobs. Power Query is the right answer when you import the same messy CSV every Monday or pull data from an API that needs the same cleanup every time. Set up the transformation once, refresh it forever. No formulas to maintain. No helper columns piling up. Just a recipe Excel runs in the background.

The path: Data tab → Get Data → From File / From Web / wherever your source lives. Excel loads the file into the Power Query Editor. Right-click the column you want to clean. The menu has two relevant options: Transform → Trim, and Transform → Clean. Trim strips leading and trailing whitespace (yes, including NBSP, unlike the Excel TRIM function — Power Query's Trim is smarter). Clean removes non-printable characters.

To strip ALL internal spaces in Power Query, you need a Replace Values step: right-click the column → Replace Values → Find a space, replace with nothing. Click OK. The transformation is now part of your query. Next week's import gets cleaned the same way, automatically.

The Custom Column Trick

For more aggressive cleanup, add a Custom Column with this M formula: Text.Trim(Text.Replace([Column1], Character.FromNumber(160), " ")). This handles NBSP specifically, then trims. Power Query's M language is more verbose than Excel formulas but gives you exact control over each step.

One downside: Power Query has a learning curve. If you've never opened the editor, plan 30 minutes for your first cleanup. After that, it's the fastest method for any recurring import job. The how to clean data in excel guide walks through the full Power Query setup for a sample CSV.

Removing Line Breaks (CHAR 10 and CHAR 13)

Multi-line cells from form submissions or pasted email content often contain hidden line breaks. CHAR(10) is line feed (LF, used on Mac/Linux). CHAR(13) is carriage return (CR, used on old Mac systems). Windows files often have both: CR+LF. They show up as wraps inside one cell — looks normal on screen, breaks every text function downstream.

To strip both in one shot: =SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(13)," "). Wrap that in TRIM if you also want extra spaces collapsed: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(13)," ")). Looks horrifying. Works perfectly. Want to keep the line break as a delimiter for splitting later? Replace with a pipe instead of a space: SUBSTITUTE(A1,CHAR(10),"|").

The Ctrl+J Shortcut for Find & Replace

Excel has a hidden trick for line breaks in the Find & Replace dialog. Open it with Ctrl+H. Click into the "Find what" box. Press Ctrl + J. Nothing visible happens — but you've just inserted a line-feed character. Leave Replace empty (or type a space), click Replace All. Every line break in the selection vanishes. Faster than building a SUBSTITUTE formula when you just need a one-time fix.

Real-World Example: Cleaning an Imported CSV

You just imported a 5,000-row CSV from an old database. Column B is product names. You notice some VLOOKUPs failing and a few sort positions look wrong. Here's the cleanup recipe in order:

First, add helper column C with =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))). That single formula handles 95% of import problems: NBSP gets normalized, non-printables get stripped, leading/trailing/extra spaces vanish. Fill down. Copy column C, paste as Values back into column B. Delete column C. Save.

Run a quick sanity check: =COUNTIF(B:B,"* *") counts any cell with double spaces. If it's zero, you're clean. If not, run TRIM again. For lookups, also apply the same formula to the source value: =VLOOKUP(TRIM(CLEAN(SUBSTITUTE(E2,CHAR(160)," "))),Table,2,FALSE). Ugly. Effective. Saves hours.

Performance Notes for Large Datasets

TRIM and SUBSTITUTE are volatile-adjacent — they recalculate whenever the workbook recalculates. On 50,000+ rows that's noticeable. Three speed tricks: paste formulas as values once the cleanup is done, switch calculation to manual (Formulas → Calculation Options → Manual) while you work, and prefer Power Query for any dataset over 100,000 rows because Power Query processes off the worksheet and won't drag your formula recalculation.

One more performance note: if you absolutely must do this with formulas on a huge sheet, use a single combined formula rather than chaining helper columns. Each helper column is another full sweep through your data. =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))) runs the three operations in one cell pass — about 3x faster than three separate columns. Memory matters too: Excel allocates roughly 8 bytes per cell for formulas plus string overhead. A 100,000-row helper column eats 1–2 MB before you've added any other data.

Bottom line on Excel remove space: try TRIM first, check for NBSP if it doesn't work, use SUBSTITUTE when you want every space gone, reach for Power Query on recurring imports. That covers 99% of jobs. The remaining 1% — Unicode zero-width spaces, ideographic spaces from Japanese text, soft hyphens — needs custom character codes plugged into SUBSTITUTE. Look up the Unicode code point, plug it into UNICHAR() or CHAR(), and you're back in business. Want to test your Excel cleanup skills on real exam-style scenarios? Take the practice quiz below.