VBA Remove Spaces from String in Excel

VBA Remove extra Spaces or blanks from String in Excel. Sometimes we may have more than one space in a string. To remove additional or extra spaces we use different VBA functions like LTRIM, TRIM, or RTRIM. We use LTRIM function to remove left most or starting of the string spaces. To remove right most or end of the string spaces we use RTRIM function. Finally we use TRIM function in VBA Remove Spaces macro to remove all extra spaces in a string.

Table of Contents:

Objective
Macro to Trim Blank Spaces from String in Excel VBA
VBA Code to remove extra Multiple Spaces from String in Excel
Instructions to Run VBA Macro Code
Other Useful Resources

Macro to remove extra Spaces from String in Excel VBA

Here is the best excel VBA remove spaces from string macro to remove spaces in Excel. Let us see the example macros to trim,delete and remove blank spaces from String in Excel VBA.

'VBA Code to delete extra Spaces from String in Excel
Sub VBA_Remove_Spaces()
    
    'Variable Declaration
    Dim String1 As String, TempString As String
    
    'There are multiple spaces in string
    String1 = "Thanks   for   visiting   our      blog   "
   
    'Remove Extra Spaces
    Do
        TempString = String1
        String1 = Replace(String1, Space(2), Space(1))
    Loop Until TempString = String1
        
    MsgBox "Removed Extra Spaces from String: " & vbCrLf & String1, vbInformation, "VBAF1"

End Sub

Here is the output screenshot of above macro procedure.

VBA Code to delete extra Spaces from String in Excel

Let us see the example VBA Code to delete extra Spaces from String in Excel.

'VBA Code to Remove Multiple Spaces from String in Excel
Sub VBA_Remove_Multiple_Spaces()
    
    'Variable Declaration
    Dim String1 As String
    Dim sResult As String
    
    String1 = "Thanks   for   visiting   VBAF1   "
   
    'Remove Extra Spaces
    sResult = Application.WorksheetFunction.Trim(String1)
    
    MsgBox "Removed Extra Spaces from String: " & vbCrLf & sResult, vbInformation, "VBAF1"

End Sub

output: Let us see the output screenshot of above VBA macro code.