VBA Remove Spaces from String in Excel

VBA Remove Spaces from String fi

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.

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 Remove Spaces from string in Excel

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.

Remove Extra Spaces

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment