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!
Trim Worksheet Function
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's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
Loop with Replace
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
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
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
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.