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.
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.
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.
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.
To overcome all these kinds of problems, we have a function called 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.
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.
Sub Trim_Example1() Dim k As String End Sub
Step 2: Now, for this declared variable, assign a value by using the TRIM.
Sub Trim_Example1() Dim k As String k = Trim( End Sub
Step 3: For this string type, the word in double-quotes as “ Welcome to VBA.”
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.
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 run manually. We should get a correct string in the message box.
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.”
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. “
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 as well.
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.
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.”
Sub Trim_Example3() Dim MyRange As Range End Sub
Step 2: Since the range data type is an object, we need to set the range first. So set the range as “selection.”
Sub Trim_Example3() Dim MyRange As Range Set MyRange = Selection End Sub
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., apply the TRIM function.
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. 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 & 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.
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.
To overcome this problem, we need to use the trim function as a worksheet function in the 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. 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 –