VBA TRIM

VBA TRIM comes under the String and Text functions. This function is a Worksheet function in VBA. Similar to the worksheet reference, this function one may use to trim or remove unwanted spaces from a string. It takes a single argument, 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 is very painful to work with. So, in regular Worksheet functions, we have TRIM in excel to eliminate "Leading Spaces, Trailing Spaces, and In-Between Space." In VBA, we also have a TRIM function, which works the same as the Excel function, but there is only one slight difference. We will see that in this article a little later.

TRIM is a string function that removes spaces in excel from the selected cell. For example, in space, we have three types: Leading Space, Trailing Space, 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.

Trailing Space is nothing, but after the end of the value in the cell, if there is any space, it is called 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 known as In-Between Space.

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

Syntax

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

Example #1

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

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

Code:

Sub Trim_Example1()

    Dim k As String

End Sub

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

Code:

Sub Trim_Example1()

    Dim k As String

    k = Trim(

End Sub

Step 3: For this String type, the word in double quotes is "Welcome to VBA."

Code:

Sub Trim_Example1()

    Dim k As String

    k = Trim(" Welcome to VBA")

End Sub

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

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

Output:

So, the TRIM function removed all the leading spaces we had entered into the string value. Hence, the correct sentence, i.e., "Welcome to VBA."

Example #2

Now, take a look at one more example to remove trailing spaces. Again, 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

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

Output:

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 one below in your Excel sheet.

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

Step 1: Declare a variable as “Range.”

Code:

Sub Trim_Example3()

    Dim MyRange As Range

End Sub

Step 2: Since the Range data type is an object, we must set the range first. So, set the range as "selection."

Code:

Sub Trim_Example3()

    Dim MyRange As Range

    Set MyRange = Selection

End Sub

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

Step 3: Now, apply the TRIM function using For Each VBA Loop.

Code:

Sub Trim_Example3()

    Dim MyRange As Range

    Set MyRange = Selection

    For Each cell In MyRange
    cell.Value = Trim(cell)
    Next
End Sub

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

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

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

Step 7: Now, the Macro names box will open. Select the name of the Macro we just created. Then, click on "OK."

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

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

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

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

In cell A3, we have in-between spaces. However, selecting and clicking on the rectangular shape will only remove trailing and leading spaces, not in-between spaces.

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

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

• VBA Trim and Worksheet Trim syntax are the same.
• It can only remove leading and trailing spaces, not in-between spaces.
• The Excel Trim function can remove all kinds of spaces.