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

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

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

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.

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 you can convert these macros to functions for increased flexibility. When you’re ready to start writing your macros and functions faster (without having to search online for answers) check out our best-selling VBA Cheat Sheets for some powerful automation tricks. For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.