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

Text is where datasets get messy: stray spaces, hidden characters, and typos. You can fix them with an AI prompt, or with Excel's own functions — this tutorial shows both routes for each job.

Table of Contents

• Remove Spaces
• Text Cleanup
• Correct Spelling

Remove Spaces

What it is. Removing unwanted spaces — leading, trailing, and double spaces inside text.

Why it is useful. To Excel, "John Smith", " John Smith", and "John Smith" are three different names. Matching and lookups silently break over spaces.

Do it with AI

The prompt.

"Trim all text columns — remove extra spaces."

Select the column or table, open Copilot, type the prompt, review, then Apply.

The result is "John Smith" in every row.

Do it manually in Excel

• TRIM function: =TRIM(A2) removes leading/trailing spaces and collapses double spaces to one.
• CLEAN removes non-printable characters — pair them: =TRIM(CLEAN(A2)).
• Find & Replace (Ctrl + H): find (a double space), replace with (a single space), Repeat until no changes — quick for small sheets.

Verify

Ask Copilot "list every column you changed" — a broad prompt can touch columns you didn't mean to. Or scan the row count and a few cells after TRIM.

Common mistake. Trimming one column while others still have the same problem. Clean all text columns at once.

Best practice. Trim before any matching, lookup, or dedupe — on arrival, not in the middle of other work.

Text Cleanup

What it is. Removing stray characters, line breaks, symbols, and hidden junk that arrive with pasted data.

Why it is useful. Copied text from websites or PDFs carries tabs, newlines, and curly quotes you can't see — and they break formulas and matching.

Do it with AI

The prompts.

• "Clean this column — remove line breaks and invisible characters."
• "Replace curly quotes with straight quotes in this sheet."
• "Remove all special characters except letters and numbers from column C."

Prompt: "Remove hidden line breaks and extra spaces from column A." → "North Sales"

Do it manually in Excel

• =TRIM(CLEAN(A2)) — CLEAN strips the invisible line-break character, TRIM tidies the spacing.
• Find & Replace (Ctrl + H) — swap curly quotes for straight ones, or remove specific symbols.
• Flash Fill (Ctrl + E) — type the clean result once and let Excel copy the pattern.

Verify

Ask Copilot "Show me what you removed" — cleaning can be over-aggressive. Or eyeball a few cells carefully after cleaning.

Common mistake. Cleaning only visible characters — invisible junk survives. Use CLEAN (or ask for a full cleanup) so hidden characters go too.

Best practice. Paste imported text as Values (Ctrl+Alt+V) so junk never arrives in the first place.

Correct Spelling

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

Why it is useful. "Recieved" and "Received" split one category into two, and typos defeat duplicate detection.

Do it with AI

The prompt.

"Fix spelling mistakes in the Status column and show me what you changed."

Every row becomes "Complete," and the AI reports the corrections it made.

Do it manually in Excel

• Spell check: press F7 to check the whole sheet.
• Find & Replace (Ctrl + H): fix known typos in bulk — find Complet, replace with Complete.
• Prevent typos: Data → Data Validation → List — a dropdown so users pick from a fixed list and typos can't happen.

Verify

Ask Copilot "Show me what you changed" and review the list — it can "correct" brand names or codes. Or run F7 after a bulk replace as a safety net.

Common mistake. Replacing "Complet" with "Complete" without Match entire cell contents — "Complete" also contains "Complet", so it becomes "Completee".

Best practice. For columns that must not change (codes, proper names), exclude them from the prompt or review every replacement.