Excel VBA TRIM Function (explained with Examples)

Excel already has a TRIM worksheet function that you can use to get rid of leading, trailing, and extra spaces in between words/strings.

There is a TRIM function in VBA as well (and it does exactly the same thing – remove extra spaces).

Why do we need a TRIM function in VBA too?

Fair question!

While you can work with the inbuilt TRIM function in Excel worksheets to get rid of spaces. in some cases, you may need to do this using VBA.

This could be as a part of a bigger code.

Syntax of the VBA TRIM function


‘String’ is the argument that can be a text string or a variable that holds a text string.

Let’s learn how to use the VBA TRIM function with a couple of examples.

Example 1 – Showing Result in a message box

Suppose you have the following text in cell A1

Excel VBA Trim Function - Examples 1 Data

Note that there are spaces before and after the sentence.

The below code would take this sentence, and show the result after trimming the extra spaces

Sub TrimExample1()
MsgBox Trim(Range("A1"))
End Sub

VBA Trim Function code result

There is one important thing to know when using the TRIM function in VBA –  it will only remove the leading and trailing spaces. It will not remove the extra spaces in between words (which the in-built TRIM function in the worksheet does).

For example, suppose you have the following text in cell A1 (with extra spaces in between words):

VBA TRIM Function - Extra spaces in between words

If I use the above VBA code, it will show a message box as shown below:

MessageBox with extra spacesAs you can see in the message box result, the extra spaces between words are not removed by the VBA TRIM function.

Then what’s the alternative?

If you want to remove leading, trailing, as well as double spaces in between words, it’s better to use the following code:

Sub TrimExample1()
MsgBox WorksheetFunction.Trim(Range("A1"))
End Sub

The above code uses the worksheet TRIM function (instead of using the TRIM function in VBA).

Note: VBA Trim function does not remove the non-whitespace characters – such as newlines (vbNewLinevbCrLf) etc.

Example 2 – Quickly Remove Leading and Trailing Spaces from a Range of Cells

Suppose you have a data set as shown below where there are leading and trailing spaces:

VBA TRIM Example 2 Dataset

You can use the below code to instantly clean this data and remove all the leading and trailing spaces:

Sub TrimExample1()
Dim Rng As Range
Set Rng = Selection
For Each Cell In Rng
Cell.Value = Trim(Cell)
Next Cell
End Sub

The above code goes through all the cells in the selection and removes the leading and trailing spaces.

Clean Data with VBA TRIM Function

You May Also Like the Following Excel/VBA Tutorials:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

4 thoughts on “Excel VBA TRIM Function (explained with Examples)”

  1. I didn’t expect to get any response to my search. Instead I got the equal most complete treatment I have come across of any issue … ever! Thanks! Loved the live ‘Run the Code’.

  2. Sub TrimCells()
    Dim Mrang As Range
    Dim Mcell As Range
    On Error GoTo hendler:
    ‘ used selected range
    Set Mrang = Intersect(Range(Selection.Address), Range(Selection.Address).Parent.UsedRange)
    For Each Mcell In Mrang
    If Len(Mcell) > 0 Then Cell = Trim(Mcell)
    ‘ all space in text
    While InStr(1, Mcell, ” “) > 0
    Mcell = Replace(Mcell.Value2, ” “, ” “)
    Next Mcell
    Set Mrang = Nothing
    Set Mcell = Nothing
    End Sub

  3. Thanks for posting this. When I copy this code, it cycles through all the cells in the range, but doesn’t perform the trim on them. Or maybe it does, but it doesn’t replace the current string in the cell, so none of the leading spaces are removed.
    Sub Trimcells()
    Dim Rng As Range
    Set Rng = Selection
    For Each Cell In Rng
    Cell.Value = trim(Cell)
    Next Cell

    End Sub


Leave a Comment