Improve VBA RTrim to Remove Trailing Spaces in Excel

The VBA RTrim function is a fast way to remove trailing spaces, but what if you need to remove all trailing whitespace, including newline characters and tabs? You can combine the RTrim and Application.Clean functions into a powerful string cleansing function to remove all trailing whitespace characters using Excel.

Here’s a function you can copy and paste into your VBA editor.

Remove Trailing Spaces and Whitespace with Excel VBA

Function TrimTrailing(str1 As Variant, Optional OnlyTrimSpaces As Boolean) As String
'------------------------------------------------------------------------------------------------------
'---DESCRIPTION: Trim trailing white space from a string.----------------------------------------------
'---CREATED BY: Ryan Wells-----------------------------------------------------------------------------
'---DATE: 11/2016--------------------------------------------------------------------------------------
'---INPUT: Pass the function a cell or a string.-------------------------------------------------------
'---OUTPUT: The output will be the original string trimmed to remove trailing white-space.-------------
'---EXAMPLE: =TrimTrailing(A1)-------------------------------------------------------------------------
'------------------------------------------------------------------------------------------------------
 
Dim i As Integer
TrimTrailing = str1
If OnlyTrimSpaces = True Then
    TrimTrailing = RTrim(TrimTrailing)
Else
    For i = Len(str1) To 1 Step -1
        If Application.Clean(Trim(Mid(str1, i, 1))) = "" Then
            'skip
        Else 'stop at first non-empty character
            TrimTrailing = Mid(str1, 1, i)
            Exit For
        End If
    Next i
End If
End Function

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

This macro goes through each character of the string you pass it and uses the VBA Trim and Application.Clean functions to remove all whitespace.

You can use the TrimTrailing function in your VBA macros and from within Excel cells. I’ll show you how!

How to use the TrimTrailing Function from a Macro

Here’s an example of how to call the TrimTrailing function from another macro:

Remove All Trailing Whitespace with a Macro

Sub Demo()
str1 = "  test " & vbTab & " " & vbNewLine & "     "
str1 = TrimTrailing(str1)
End Sub

In the above example, you have a weird string with leading spaces, trailing spaces, a tab, a new line character and more trailing spaces. The original length of the string is 16 characters.

Once you call the TrimTrailing function, all the trailing whitespace, including the vbTab and vbNewLine characters are removed. Everything on the right side of “test” is removed!

The new length of the string is 6 characters. It’s 6 characters because there are two blank spaces before the word test. Here’s a macro that prints the original and new length of the string to prove I’m not pulling your leg:

Sub DemoProof()
str1 = "  test " & vbTab & " " & vbNewLine & "     "
   Debug.Print "Original length of string: " & Len(str1) & " characters"
str1 = TrimTrailing(str1)
   Debug.Print "New length of string:      " & Len(str1) & " characters"
End Sub

Remove Only Trailing Spaces with a Macro

You may have noticed I included an optional argument in the TrimTrailing function. If this optional argument set to True, this will cause your macro to ONLY trim trailing spaces. It won’t trim trailing tab characters and new line characters. Including this argument makes the TrimTrailing function identical to the VBA RTrim function.

Sub Demo2()
str1 = "  test " & vbTab & " " & vbNewLine & "     "
str1 = TrimTrailing(str1, True)
End Sub

Again, here’s proof:

How to use the TrimTrailing Function in Excel

If you recall, I promised you can use this user-defined function in Excel, too. You can!

Remove All Trailing Whitespace from a Cell in Excel

Let’s say you have a value entered into cell A1 that is two lines long. For whatever reason, the second line only contains spaces. Work with me here… I know this is a silly example. The overall length of the text in cell A1 is 12 characters. There are two LEADING spaces in front of the text you’re interested in, but you only want to remove the trailing spaces.

Now that we’ve painted a mental picture, let’s show you what it looks like in Excel:

To use the TrimTrailing function in Excel, you have to copy the example function to your VBA editor in your spreadsheet.

Once you do that, all you do is enter a formula into a cell in Excel like

=TrimTrailing(A1)

To reinforce this concept, here’s a screenshot showing how it looks in Excel.

Notice the length of the string is now 6 characters. Again, this function removes ALL trailing whitespace, including new line characters and tabs. Why 6 instead of 4? Remember, our original string contained two empty spaces before the word test. I did that to demonstrate that the function really does only remove trailing whitespace! The leading spaces aren’t touched.

Remove Only Trailing Spaces from a Cell in Excel

For whatever reason, now you want to only remove trailing spaces, but not all trailing whitespace characters. You would do that by entering TRUE for the optional argument. Here’s how it looks on Row 5:

By including TRUE in the optional argument, the function knows to stop trimming characters once it hits the first character from the right that isn’t a space. In this case, it’s the new line character. The length of the string with only the spaces removed is 10 characters.

I hope you found this VBA tutorial helpful! If you’re eager to learn more, check out all my VBA String Manipulation Tutorials.

While you’re at it, join our VBA Insiders using the form below. After that, share this article on social media and follow me on Twitter for even more great VBA content.