Remove Extra Spaces from Selection Using Macros In Excel

If you want to quickly remove extra spaces at the start and end of your text, Excel Macros will do that for you! It is very similar to the TRIM formula as well.  Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step!

Key Takeaways

• Clean Up Data Instantly – Macros can quickly remove leading, trailing, and multiple spaces from a selected range.

• Improve Data Accuracy – Removing extra spaces helps prevent lookup errors and mismatches in formulas.

• Streamline Data Imports – Automatically tidy up text-heavy data copied from websites or reports.

• Run on Demand – Assign the macro to a button or shortcut to clean any selection with one click.

• Boost Spreadsheet Performance – Cleaner data means faster formulas and reduced chance of user error.

Quick Overview

What does it do?

Removes spaces at the start and end from your selection

Copy Source Code:

Sub RemoveExtraSpacesFromSelection()

Dim range As Range
Dim cell As Range

Set range = Selection

'loop through each cell and trim them
For Each cell In range
If Not IsEmpty(cell) Then
cell = Trim(cell)
End If
Next cell

End Sub

Final Result: 

These are our text that we want to clean up:

STEP 1: Go to Developer > Code > Visual Basic

 

STEP 2: Paste in your code and Select Save. Close the window afterwards.

 

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

 

Make sure your text and macro are both selected. Click Run.

 

With just one click, all of the unneeded extra spaces are all removed!

 

 

Frequently Asked Questions

What types of spaces does this macro remove?
It removes leading spaces, trailing spaces, and extra spaces between words, leaving only single spaces where appropriate.

Will it affect formulas in the selection?
No, the macro typically works only on cell values, not on formulas, unless specifically instructed.

Can I undo the changes after running the macro?
Macros can’t be undone with Ctrl + Z, so it’s best to save your file or create a backup first.

Do I need to select cells first before running the macro?
Yes, the macro acts on the currently selected range. Highlight the cells you want to clean before running it.

Is there a built-in Excel feature that does this?
The TRIM function does similar cleaning one cell at a time, but a macro applies it across a full selection instantly.