Remove Leading and Trailing spaces from all cells of Excel

excel

#1

While extracting data from my internal system (CRM) in excel, we have unnecessary leading and trailing spaces in each cell. Do we have any method that can remove these spaces automatically from all the cells in one go? I know one method, applying trim function to each column but it takes time.

Thanks,
Manuel


#2

@Manuel,

It can be solved in various ways, you already discussed about one of the method (apply trim function to each column). Let’s look at the other ways:

  • Use Find & Replace feature if your data values doesn’t contain any spaces. Remove space with blank.

  • Use below code and execute, it will remove all extra spaces.

    Sub removespace()
    Dim rng As Range, wk As WorksheetFunction
    Set wk = Application.WorksheetFunction

    For Each rng In Cells.SpecialCells(xlCellTypeConstants)
    With rng
    .Value = wk.Trim(.Value)
    End With
    Next ng
    End Sub

Thanks, Mark