VBA Remove Spaces from String fi

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.

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

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

Remove Extra Spaces

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above specified output screenshot.

Other Useful VBA Text and String Functions

Click on below specified links to learn more about VBA functions.

VBA Text and String Functions VBA LTRIM Function VBA TRIM Function VBA RTRIM Function




Leave a Comment

Your email address will not be published. Required fields are marked *