📥 Learn how to remove extra spaces in Excel with hands-on practice
Want to get the most out of this guide? Download the free practice workbook 👉 HERE and follow along step by step.
Why Extra Spaces in Excel Cause Problems
Have you ever copied data into Excel and found that searching, filtering, or formulas don’t work as expected? Hidden spaces are often the culprit!
Excel treats every space as a character, even if you can’t see it. These spaces can break formulas, cause incorrect sorting, and make data look inconsistent.
Before we fix the problem, let’s look at the four types of spaces that could be messing up your Excel file.
Understanding Spaces in Excel (4 Types to Watch For)
- Leading spaces – Spaces at the beginning of a cell.
📌 Example:" Sales Report"(has two spaces before the text) - Trailing spaces – Spaces at the end of a cell.
📌 Example:"Total Revenue "(has extra spaces at the end) - Extra spaces between words – More than one space between words.
📌 Example:"Q1 Report"(has multiple spaces between Q1 and Report) - Non-breaking spaces – Special spaces that look normal but behave differently.
📌 Example: Data copied from web pages, PDFs, or external sources often contain these spaces, which TRIM won’t remove.
Where Do These Extra Spaces Come From?
Spaces sneak into Excel data for many reasons:
- Copy-pasting from other sources (web pages, PDFs)
- Manually entering data with extra spaces
- Imported files with inconsistent formatting
The good news? You can easily remove extra spaces in Excel. Let’s go step by step!
Remove Extra Spaces with the TRIM Function
The TRIM function is the easiest way to clean up spaces. It removes all extra spaces except for single spaces between words.
Formula
=TRIM(text)- text – The cell or text you want to clean up.
Example: How to Use TRIM in Excel
- Download our practice file to follow along.
- Imagine you have this text in cell B1:
" Hello Excel learner "(extra spaces everywhere!)
- To clean it up, enter this formula in C1:
=TRIM(B1)
✅ Cleaned-up result: "Hello Excel learner"
Now, all leading, trailing, and extra spaces are gone, but the words still have a single space between them.
👉 Best for: Removing extra spaces but keeping proper spacing between words.
Remove Specific Characters in Excel with SUBSTITUTE
Have you ever cleaned up data in Excel but TRIM didn’t fix the issue? That’s because TRIM only removes extra spaces—but what if you need to delete other characters like commas, hyphens, or even specific words?
That’s where the SUBSTITUTE function comes in. It lets you find and replace specific characters inside a cell—no matter where they appear.
When Should You Use SUBSTITUTE?
Use SUBSTITUTE when you need to:
- Remove unwanted symbols (e.g., hyphens, commas, or extra dots)
- Remove all spaces – Unlike TRIM, SUBSTITUTE can delete every space in a cell.
- Delete extra words (e.g., remove “N/A” or “Test” from a list)
- Standardize data (e.g., replace all underscores
_with spaces)
👉 Best for: Cleaning up imported data, fixing formatting issues, and standardizing text in Excel.
How to Use the SUBSTITUTE Function in Excel
📌 Formula:
=SUBSTITUTE(text, old_text, new_text, [instance_num])- text – The cell containing the text you want to modify.
- old_text – The character or word you want to replace.
- new_text – The new character or word to replace it with.
- instance_num (optional) – Which occurrence of old_text to replace (default is all).
Practical Example: Removing Dollar-Signs from Numbers
Let’s say you have a list of prices copied from a website, but they include dollar signs:
Steps:
- Select the cell where you want the cleaned number to appear (e.g., C1).
- Enter the formula:
=SUBSTITUTE(B1, "$", "")
SUBSTITUTE removes the $ but Excel still treats the result as text, not a number.
- Before:
$12.99➝12.99(but still text ❌)
Use SUBSTITUTE + VALUE to Fix the Issue
To remove symbols and keep the numbers usable, use the VALUE function along with SUBSTITUTE.
=VALUE(SUBSTITUTE(B1, "$", ""))
- Copy the formula down to the other cells:
The values are now actual numbers. You will be able to sum them, apply formulas, or format them correctly.
How to Fix Excel TRIM Not Removing Spaces
Not all spaces are the same although the may look the same to you. The TRIM function removes spaces, but only regular spaces (ASCII 32).
However, Excel also recognizes non-breaking spaces (ASCII 160), which TRIM doesn’t remove. These often appear when copying data from:
✅ Web pages
✅ Emails
✅ Word documents
Since Excel treats non-breaking spaces differently, TRIM fails to remove them, causing lookup functions like VLOOKUP, MATCH, and XLOOKUP to break.
Step 1: Find Out If Your Cell Has Non-Breaking Spaces
To check if your space is causing issues, use the CODE function.
- Let’s assume your text with leading spaces is in column A.
- In B1, enter:
=CODE(A1)- If the result is 32 → It’s a normal space (TRIM can remove it).
- If the result is 160 → It’s a non-breaking space (TRIM won’t work).
Step 2: Fix the Issue with SUBSTITUTE + TRIM
Since TRIM only removes normal spaces, we need to first replace non-breaking spaces with regular spaces, then apply TRIM.
📌 Fixed Formula:
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
How It Works:
SUBSTITUTE(A1, CHAR(160), " ")→ Replaces non-breaking spaces with normal spaces.TRIM()→ Now that all spaces are normal, TRIM can remove extra spaces.
Use Power Query to Remove Extra Spaces
If you’re working with large datasets, manually removing spaces with formulas can slow down Excel and be hard to manage.
This is where Power Query shines! It automates data cleaning, ensuring consistent formatting without needing extra formulas.
Want to see why Power Query is a must-have skill for Excel users? Check out this detailed guide and start automating your data cleaning today!
Featured Course
Master Excel Power Query – Beginner to Pro
Download the Free Practice File
Want to try these steps in Excel without messing up your own data? We’ve got you covered!
📥 Download our free practice file to follow along with the tutorial and test each method yourself.
👉 Click here to download the Excel practice file
Using a practice file makes it easier to learn how to remove extra spaces in Excel without worrying about making mistakes in your own spreadsheets.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
