Here are three ways to remove multiple spaces in a string or a range using VBA. These macros will take 2 or more spaces and reduce them down to one space. This is great for working with fixed width text files when you want to split the results into an array with a single space delimiter.

In this tutorial, I’ll present three macros as standard subroutines. My challenge to you is to convert the subroutines into functions so that they’ll be more flexible for you when you need them. Leave a comment with your solutions or submit your functions to me via wellsrPRO!


Method 1: Trim Worksheet Function
Method 2: Loop with Replace
Method 3: Find and Replace
Closing Thoughts


Trim Worksheet Function

Method 1

This method uses the Excel worksheet function TRIM to reduce multiple spaces into a single space. The Excel worksheet function behaves a lot different than the VBA Trim function. The Excel function actually trims spaces in the middle of your string instead of just at the ends!

Sub RemoveSpaces_1()
'Trim Worksheet Function
str1 = "a    b   c   d" 'should be multiple spaces between characters
str1 = Application.WorksheetFunction.Trim(str1)

'display results
Debug.Print str1
End Sub

Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.

I want to get your free VBA material


Loop with Replace

Method 2

This solution loops through your string until it can no longer find a double space in your string. Each iteration through your string removes one of the white spaces between the characters in your string. For example, the first time through " " becomes " " and the second time through, the double space (" ") is reduced to a single space (" ")

Take a look:

Sub RemoveSpaces_2()
'Loop with replace
str1 = "a   b   c   d" 'should be multiple spaces between characters
Do
    temp = str1
    str1 = Replace(str1, Space(2), Space(1))
Loop Until temp = str1

'display results
Debug.Print str1
End Sub

Find and Replace

Method 3

This approach uses a recursive call to the subroutine to repeatedly find and replace double spaces with single spaces until eventually only single spaces remain. It does this by using the VBA Find and Replace functions, which is pretty fast for removing multiple spaces in a large range of data.

Here’s how it looks:

Sub RemoveSpaces_3()
'Remove multiple spaces from a range
Dim r1 As Range
Set r1 = ActiveSheet.Range("A1:D5") 'change this line to match your range
r1.Replace _
      What:=Space(2), _
      Replacement:=" ", _
      SearchOrder:=xlByColumns, _
      MatchCase:=True
Set r1 = r1.Find(What:=Space(2))
If Not r1 Is Nothing Then
   Call RemoveSpaces_3
End If
End Sub

Change the variable r1 to correspond to whatever range of data you need to trim the spaces on.


Closing Thoughts

If you’re an avid wellsr.com reader, you know I do a lot of work on Unix/Linux operating systems as a nuclear engineer. The overwhelming majority of the macros I create for Excel take data copied to my clipboard from these non-Windows operating systems and manipulates the data so I can use it in Excel. Removing excess spaces in these files is extremely helpful to me and I hope you find just as much use out of the solutions in this tutorial.

Remember to convert these macros to functions for increased flexibility and share them with me in the comments section or via wellsrPRO so the rest of the community can benefit.

I hope you’ll reach out to me via my VBA Consulting page if you’re working on a macro, but you’re too busy to get it done.

I also hope you’ll share this article with your friends on Facebook,and Twitter.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some powerful automation products. Many of them are free!