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 function, we have a function called TRIM 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 little later.
What Does Trim Function Do?
TRIM is a string function which removes unwanted 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 as 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 kind 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 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 F5 key or run manually we should get a correct string in the message box.
So, TRIM function removed all the leading spaces we have entered to 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 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 code. 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 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 Loop apply 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 assign 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 VBA Trim & Excel Trim Function
I know you already thinking what’s the difference between VBA Trim & Excel Trim function is.
The only difference between them is VBA function cannot remove in between spaces while worksheet function does. For an 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 worksheet function trim not VBA trim. 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 VBA TRIM function to remove spaces such as Leading and Trailing space along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –