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

---

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions

or looking for a job ?
 Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs

How to Shrink or Reduce the size of Excel Workbook without losing data?

Learn: VLOOKUP         OFFSET        Custom Format        Array Formula

---