Keep leading and remove only the trailing spaces

Jeff recently asked: I really like your product but just came upon a capability it is missing: Delete trailing spaces (but keep all other spaces). I expect it would be simple enough to add; would you?

I never expected anyone to need a tool to only remove the trailing spaces but he needs it on imported data that is indented and therefore the leading spaces should remain.

As you probably know, the TRIM function will remove all leading and trailing spaces from a value. Well, Excel VBA also has the LTRIM and RTRIM functions which will either remove all the spaces from the left (leading) or from the right (trailing).

The following macro will do the trick:

Sub TrimTrailingSpaces()
    ' usage:
    ' 1. select the cells where the trailing spaces should be removed
    ' 2. run this macro
    Dim rngCel                                       As Range
    For Each rngCel In Selection.Cells
        rngCel.Value = RTrim(rngCel.Value)
 End Sub

Comments are closed.