The Best Ways to Remove Blank Spaces in Excel
Master Excel data cleaning with TRIM, SUBSTITUTE, and filtering techniques to eliminate unwanted spaces and improve accuracy.
Working in Excel becomes frustrating when your data contains unwanted blank spaces. These spaces can affect formulas, sorting, filtering, and overall data accuracy. If you're wondering how to remove blank spaces in Excel, this guide will show you the simplest and fastest methods you can use — even if you're a beginner.
1. Remove Leading and Trailing Spaces
Sometimes Excel cells contain spaces before or after the text. These are not visible, but they break formulas like VLOOKUP or MATCH.
Use the TRIM Function
- Select an empty cell.
- Type:
=TRIM(A1) - Press Enter.
- Drag the fill handle down to apply it to the full column.
The TRIM function removes:
- Extra spaces
- Leading spaces
- Trailing spaces
Then, copy → paste as values to replace the original data.
2. Remove All Extra Spaces Inside Text
TRIM only removes leading/trailing spaces and reduces multiple spaces to one. If you want to remove all spaces entirely, use:
SUBSTITUTE Function
=SUBSTITUTE(A1, " ", "")This deletes every space, useful for:
- Phone numbers
- IDs
- Codes
- URLs
3. Remove Blank Rows in Excel
Sometimes blank rows interrupt your dataset. To clean them:
Method: Filter and Delete
- Select the entire sheet or column.
- Go to Data → Filter.
- Click the filter arrow.
- Choose Blanks.
- Select all blank rows → Right-click → Delete Rows.
- Remove the filter.
Your data becomes clean instantly.
4. Remove Hidden Spaces Causing Issues
Excel sometimes contains invisible characters like line breaks or non-breaking spaces.
Clean Using CLEAN + TRIM
=TRIM(CLEAN(A1))This removes:
- Line breaks
- Hidden characters
- Extra spacing
Perfect for messy, imported datasets.
5. Remove Blank Spaces Using Find & Replace
For quick cleaning:
- Press Ctrl + H.
- In Find what, type one space.
- In Replace with, leave it empty.
- Click Replace All.
This removes every visible space instantly.
Final Thoughts
Knowing how to remove blank spaces in Excel helps you clean data faster and avoid formula errors. Whether you're removing empty rows, hidden characters, or unwanted spacing, Excel provides easy tools to fix everything. With TRIM, SUBSTITUTE, CLEAN, and filtering, your dataset can be polished in just a few clicks.