Remove Extra Spaces from a Cell Value - Text VBA Macro



Left Trim

Description: This module will remove extra spaces from left side of selected cells. For e.g if a cell value is "    excel    " then after running the macro it will become "excel    ". (Notice spaces from left side has been removed.)
Code:
Sub TrimLText()
' This module will trim extra spaces from LEFT SIDE.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = LTrim(MyCell.Value)
        Next
    On Error GoTo 0
End Sub


Right Trim
Description:This module will remove extra spaces from right side of selected cells. For e.g if a cell value is "      excel   " then after running the macro it will become "     excel". (Notice spaces from right side has been removed.)
Code:Sub TrimRText()
' This module will trim extra spaces from RIGHT SIDE.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = RTrim(MyCell.Value)
        Next
    On Error GoTo 0
End Sub


Trim from Both sides
Description:This module will remove extra spaces from both sides of selected cells. For e.g if a cell value is "      excel   " then after running the macro it will become "excel". (Notice spaces from both sides has been removed.)
Code:Sub TrimBText()
' This module will trim extra spaces from BOTH SIDES.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Trim(MyCell.Value)
        Next
    On Error GoTo 0
End Sub


Remove Extra Excessive Spaces
Description:This module will remove extra spaces from both sides and from inside the selected cells. For e.g if a cell value is "      excel       items   " then after running the macro it will become "excel items". (Notice spaces from left , right and between excel and items words has been removed.)
Code:Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "     ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "    ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "   ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "  ", " ")
        Next
    On Error GoTo 0
End Sub




Comments

María Lucía said…
Hi!! I need to use this macro!! Its just what I need! The problem... is that.. I copy-pasted the code for left-trim and its not working... I don´t know what exactly I have to modify from the code in order to make it work on my file. Can you help? I want to apply this to delete the spaces to the left from ALL the cells in my file.
Ashish Jain said…
Thanks Maria for your question. Please try it like this:

1. Copy the VBA Macro in your excel file Visual Basic Editor (Using Alt+F11)
2. Come back to Excel worksheet.
3. Select those cells which you want to trim.
4. Press Alt+F8 to open Macros window.
5. Select TrimLText and click on Run.

Hope this helps else do let me know :)

Regards
Ashish Jain
Debt Collection said…
This causes my program to run continuously. It just gets stuck any ideas? Thanks for the help.
Anonymous said…
select ur data then run the macro.
Anonymous said…
Debt Collection,
I found similar macros elsewhere. I have tried a few but I have found that it takes quite a while to process even just a few cells. Perhaps you just have to wait for it to finish. Epically if you are using it on a large number of files.

Does anyone have any idea as to how this may be speeded up?

One commercial AD-IN I tried did a whole column in a fraction of the time that this macro takes to do a few cells.
Anonymous said…
Thanks a lot for the right Trim