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
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.
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 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.
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!