Excel VBA TRIM Function

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 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 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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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. 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.

Examples

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 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.

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.”

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 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. “

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.

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.”

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.”

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 LoopVBA LoopA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more.

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.

Difference Between Excel & VBA Trim Function

The only difference between them is that the 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.

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.

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 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. It will remove all kinds of spaces.

Things to Remember

  • 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.

This article 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, with practical examples and a downloadable Excel template. Below you can find some useful Excel VBA articles: –

  • Excel VBA String FunctionsVBA DIR FunctionFIND Function in VBACell References in VBA