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 excelTRIM In ExcelThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces.read more 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 excelRemoves Spaces In ExcelWhile importing or copy-pasting the data from an external source, extra spaces are also copied in Excel. This makes the data disorganized and difficult to be used. The purpose of removing unwanted spaces from the excel data is to make it more presentable and readable for the user. read more 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 excelRemove Leading Spaces In ExcelThe TRIM function in Excel can remove all the unwanted spaces in the excel cell. read more 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 LoopA VBA loop in excel is an instruction to run a code or repeat an action multiple times.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 VBAWorksheet Function In VBAThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more 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 functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. 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 >>