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

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

VBA Trim Trailing Whitespace

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 Demo()
str1 = "  test " & vbTab & " " & vbNewLine & "     "
str1 = TrimTrailing(str1, True)
End Sub

Again, here’s proof:

VBA RTrim Trailing Spaces

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:

Excel Demo for Trimming Strings

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.

Excel Remove Trailing Whitespace

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:

Excel Remove Trailing Spaces

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.

Submit a comment below if you like this macro or have more questions. As always, subscribe to my email list, share this article on social media and follow me on Google+ and Twitter for more great VBA content!


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.