VBA TRIM

Excel VBA TRIM Function

VBA TRIM is categorized under the string and text functions, this function is a worksheet function in VBA and similar to the worksheet reference this function is used to trim or remove the unwanted spaces from a string, it takes a single argument which is an input string and returns an output as a string.

There are several situations where we download the data from online servers, and we face a situation of unorganized data and unwanted spaces in the cell. Dealing with unnecessary spaces are very painful to work with. In regular worksheet functions, we have a function called TRIM in excel to get rid of “Leading Spaces, Trailing Spaces, and in-between space.” In VBA, also we have a function called TRIM, which works exactly the same as the excel function, but there is only one slight difference is there. We will see that in this article a little later.

VBA TRIM

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA TRIM (wallstreetmojo.com)

What Does Trim Function Do?

TRIM is a string function that removes spaces in excel from the selected cell. In spaces, we have 3 types “Leading Spaces, Trailing Spaces, and in-between space.”

Leading Space is nothing, but before any value starts in the cell, if there is any space, then it is called Leading Space.

Leading space

Trailing Space is nothing but after the end of the value in the cell if there is any space, then it is called as Trailing Space.

Trailing space

In-Between Space is nothing after the end of each word; ideally, we should have one space character. Anything more than one space character is called In-Between Space.

In Between space

To overcome all these kinds of problems, we have a function called TRIM.

Syntax

VBA Trim

String: What is the string or value you want to trim? It could be cell reference as well as a direct supply of value to the formula.

Examples

You can download this VBA TRIM Function Template here – VBA TRIM Function Template

Example #1

Now we will see how to remove leading spaces in excel using the TRIM function. Follow below steps

Step 1: Create a macro name and declare a variable as String.

Code:

Sub Trim_Example1()

    Dim k As String

End Sub
VBA Trim Example 1

Step 2: Now, for this declared variable, assign a value by using the TRIM.

Code:

Sub Trim_Example1()

    DimAs String

    k = Trim(

End Sub
VBA Trim Example 1-1

Step 3: For this string type, the word in double-quotes as “   Welcome to VBA.”

Code:

Sub Trim_Example1()

    Dim k As String

    k = Trim(" Welcome to VBA")

End Sub
VBA Trim Example 1-2

Step 4: Now show the result of this variable in the message box.

Code:

Sub Trim_Example1()

    Dim k As String

    k = Trim(" Welcome to VBA")

    MsgBox k

End Sub
VBA Trim Example 1-3

Step 5: Run this code using the F5 key or run manually. We should get a correct string in the message box.

VBA Trim Example 1-4

Output:

VBA Trim Example 1-5

So, the TRIM function removed all the leading spaces we have entered into the string value. Now the result is a correct sentence, i.e., “Welcome to VBA.”

Example #2

Now take a look at one more example to remove trailing spaces. Take the same word but this time, enter trailing spaces.

“Welcome to VBA. “

Code:

Sub Trim_Example2()

    Dim k As String

    k = Trim("Welcome to VBA ")

    MsgBox k

End Sub
VBA Trim Example 2

Run this code manually or through the F5 key Trim function removes trailing spaces as well.

VBA Trim Example 2-1

Output:

VBA Trim Example 2-2

Example #3 – Clean the Data in Cells

Now we will see the practical example of cleaning the data in excel cells. Assume you have a data set like the below on in your excel sheet.

VBA Trim String Example 3

When we have more than one cell data, we need to write different codes. Follow the below steps to write code.

Step 1: Declare a variable as “Range.”

Code:

Sub Trim_Example3()

    Dim MyRange As Range

End Sub
VBA Trim String Example 3-1

Step 2: Since the range data type is an object, we need to set the range first. So set the range as “selection.”

Code:

Sub Trim_Example3()

    Dim MyRange As Range

    Set MyRange = Selection

End Sub
VBA Trim String Example 3-2

Note: MyRange = Selection means whatever the cells I select becomes a range. So in that range, TRIM removes unwanted spaces.

Step 3: Now, using For Each VBA LoopVBA LoopLoops are commonly used in all of the programming languages, where there is a certain need or a criteria when we need a certain code to run a certain times. In VBA, there are several different types of loops, including Do while, Do till, For Loop, and For each loop. These loops help code to execute until the condition is met.read more, apply the TRIM function.

Code:

Sub Trim_Example3()

    Dim MyRange As Range

    Set MyRange = Selection

    For Each cell In MyRange
    cell.Value = Trim(cell)
    Next
End Sub
VBA Trim Example 3-3

Step 4: Now go back to the worksheet. Go to insert and draw a rectangular shape.

VBA Trim Example 3-4

Step 5: Add a word to the inserted rectangular shape as click here to clean the data.

VBA Trim Example 3-5

Step 6: Right-click on the rectangular shape and select the assigned macro.

Example 3-6

Step 7: Now, the macro names box will open. Select the name of the macro we just created. Click on Ok.

Example 3-7

Step 8: Now select the range where we need to clean the data.

Example 3-8

Step 9: After selecting the range, click on the assigned macro rectangular box. It will clean the data.

Example 3-9

So we got cleaned value, i.e., after removing leading & trailing spaces.

Difference Between Excel & VBA Trim Function

The only difference between them is VBA function cannot remove in between spaces while the worksheet function in VBA does. For example, take a look at the below example.

Example 3-10

In cell A3, we have in-between spaces. If select and click on the rectangular shape, it will only remove trailing & leading spaces, not in-between spaces.

Example 3-11

To overcome this problem, we need to use the trim function as a worksheet function in the code.

Code:

Sub Trim_Example3()

    Dim MyRange As Range

    Set MyRange = Selection
  
    For Each cell In MyRange
    cell.Value = WorksheetFunction.Trim(cell)
    Next

End Sub
Example 3-12

Here we have used a worksheet function, not a VBA function. This will remove all kinds of spaces.

Things to Remember

  • VBA Trim & Worksheet Trim syntax is exactly the same.
  • It can only remove leading & trailing spaces, not in-between spaces.
  • Excel trim function can remove all kinds of spaces.

This has been a guide to VBA TRIM Function. Here we learn how to use the TRIM function to remove spaces such as Leading and Trailing space along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>