Saturday, July 3, 2010

Macro to trim data

If you want to trim  selected cells in one go . Try this macro

Sub trim_selected_range()

Dim cell_1 As Range

For Each cell_1 In Selection.Cells
    If cell_1.HasFormula = False And Not IsEmpty(cell_1) And Not IsDate(cell_1) Then
        cell_1.Value = Application.WorksheetFunction.Trim(cell_1.Value)
    End If

End Sub

Steps to Use

  • Copy the below code
  • Press Alt+F11 to open VBA editor
  • Paste it in any public module or module 1
  • Select the cells & run the Macro


  1. I have been playing with routine and it is very useful, especially with imported data. It can also be used to capitalise lists (names for example) or to convert numbers to currency. Simply replace Function.Trim with a different function e.g. Function.Proper (for capitalisation) or Function.Dollar for currency.

    There are several other functions that could easily be used; Clean, Dollar, Fixed, Proper, and Round. IsError type functions would also work, as would find and replace type functions.

  2. You don't need this: Application.WorksheetFunction. as Trim is also a VBA function. No need to call the Applications worksheet level function :). I would probably also use .formula and .text instead of .value to stop it from resolving textual numbers (ie UPC's and catalogue codes)