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