Sometimes we download data from online servers, and the data needs to be in the correct order, or there are extra spaces in the cell. Working with spaces that don’t need to exist is challenging. The TRIM function in VBA helps eliminate any additional spaces in your data. It will eliminate all the extra areas between your String’s characters. So, in Excel, we use the TRIM function to eliminate “leading spaces, trailing spaces, and in-between spaces” in regular Worksheet functions. There is also a TRIM function in VBA. It works the same way as the Excel function, with a tiny difference.
The TRIM function in VBA helps to get rid of any extra spaces in your data. The syntax for both VBA Trim and Worksheet Trim is the same. It can only eliminate spaces at the start and last of a word. We can trim a lot of data at once without looking at how many extra areas are in the cells. There is little chance that any extra space will be saved.
Syntax
TRIM (String)
Parameter
String (Required): The parameter is the String from which you will remove extra spaces at the beginning, end, and in between.
The Work of the Trim Function
The TRIM function helps get rid of three different kinds of spaces, such as:
1. Leading Space: The spaces that come before a value in a cell are called leading spaces (if any).
2. Trailing Space: The spaces that come after the last number in a cell are called trailing spaces (if any).
3. In-Between Space: In-between Space is the space that comes at the last of each word. In-Between space is anything that has more than one space character. By default, there should be one space character after every word.
Example
Get rid of the extra spaces in the String. ” Hello World”
Here are the steps to write a VBA macro to use the VBA TRIM function to get rid of any extra spaces in a given string:
Step 1: Open the tab for the VBA developer. Go to the Excel worksheet, click on the ribbon tab, and then click on the developer window. Tap on the visual basic editor or press Alt+F11.
Step 2: You will see the VB Editor window. Next, you need to do a module. Click on Insert on the ribbon, then click on Module.
Step 3: Insert the Moule window. Start with the macro name and then the variable declaration.
Sub VBA_TRIM_Function() Dim str_Value As String End Sub
Step 4: The next step is to provide the TRIM function with the desired String. Save the result of the TRIM function in the variable. When finished, the output will be shown using the VBA MsgBox function.
Sub VBA_TRIM_Function()
Dim str_Value As String
'The Trim function will eliminate the unwanted space characters from the text
str_Value = Trim(" Hello World")
MsgBox str_Value
End Sub
Output
You can activate the macro by selecting Run from the VBA ribbon toolbar or pressing the F5 key. A MsgBox will be displayed in Excel immediately.
