Text Cleaning in Excel: Remove Spaces, Clean Up Junk & Fix Spelling

Text is where datasets get messy: stray spaces, hidden characters, and typos. These look small, but they quietly break lookups, matching, and analysis. This tutorial covers removing unwanted spaces, cleaning up junk characters, and fixing spelling.

Table of Contents

• Remove Spaces
• Text Cleanup
• Correct Spelling

Remove Spaces

What it is. Stripping the spaces that don't belong — leading, trailing, and double spaces inside text.

Why it is useful. To Excel, "John Smith", " John Smith", and "John Smith" are three different names. Lookups fail and duplicate checks miss them.

Syntax.

• TRIM(text) — removes leading/trailing spaces and collapses double spaces to one.
• CLEAN(text) — removes non-printable characters.
• SUBSTITUTE(text, " ", "") — removes all spaces, even between words.

Example.

=TRIM(A2) → John Smith

Explanation. TRIM cleans the edges and squeezes the double space in the middle into a single space, leaving one tidy name.

Best practice. Run TRIM the moment data arrives, before any lookup or match. Pair it with other functions: =TRIM(CLEAN(A2)).

Notes. Since Excel 2019, TRIM also removes non-breaking spaces (character 160). For older versions use =SUBSTITUTE(A2, CHAR(160), "").

Let AI do it. Copilot: "Trim all text columns." — one prompt cleans the whole sheet.

Text Cleanup

What it is. Removing stray characters, line breaks, symbols, and invisible junk that arrive with pasted data from web pages, PDFs, or other systems.

Why it is useful. Copied text often carries tabs, newlines, or curly quotes that you can't see but Excel can — and they break formulas and matching.

Techniques.

• CLEAN() to strip non-printable characters and line breaks.
• TRIM() afterwards for the spaces left behind.
• SUBSTITUTE() to swap specific characters, like curly quotes → straight quotes.
• Find & Replace (Ctrl + H) for global swaps.
• Flash Fill (Ctrl + E) to rebuild a column from one example.

Example. A2 was pasted from a website and contains a hidden line break: "North↵Sales". =TRIM(CLEAN(A2)) → North Sales

Explanation. CLEAN removes the invisible line-break character; TRIM tidies the spacing that remains.

Best practice. Paste as plain values (Ctrl + Alt + V → Values) when importing, so junk never arrives in the first place.

Common mistake. Cleaning with Find & Replace for visible characters only and missing the hidden ones. Always pair CLEAN + TRIM on imported data.

Correct Spelling

What it is. Fixing misspelled words in your data so every variant of a word becomes one correct version.

Why it is useful. "Recieved" and "Received" split one category into two. Typos also defeat duplicate detection — a misspelled repeat looks like new data.

Techniques.

• Press F7 to run Excel's spell check across the sheet.
• Find & Replace (Ctrl + H) to fix known typos in bulk.
• Data validation dropdowns so users pick from a fixed list and typos can't happen.

Example. A "Status" column mixes "Complet", "Complete", and "Compleet".

Find & Replace: Find Complet, Replace with Complete → all rows now say "Complete". Run F7 afterwards as a safety net.

Explanation. Bulk replace fixes the two typos at once, and spell check catches anything left over.

Common mistake. Running the replace without Match entire cell contents. "Complete" also contains "Complet", so a careless replace turns it into "Completee". Match the whole cell, or use =IF(A2="Complet","Complete",A2).

Best practice. Stop the problem at the door: a dropdown (Data → Data Validation → List) prevents misspellings from being entered at all.

Let AI do it. Copilot: "Fix spelling mistakes in the Status column." — it fixes known typos and can explain what it changed.