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 to remove all extra spaces in a string.
Let us see the example macro to trim/delete/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.
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.
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.
Click on below specified links to learn more about VBA functions.