The TRIM function is exclusively used to remove three types of spaces, which are as follows:

1. Leading Space: Leading Space is defined as the spaces that occur before any value starts in the cell (if any).
2. Trailing Space: Trailing Space is defined as the spaces that occur after the end of the value in the cell (if any).
3. In-Between Space: In-between Space is defined as the spaces that occur after the end of each word. By default, after every word, there should ideally be one space character. Anything more than one space character is called In-Between Space.

This function is useful to eliminate the spaces at the beginning and end of the string as they cannot be easily seen until and unless the user goes to the edit mode of that specific cell. VBA TRIM Function is categorized under String type variable.

NOTE: VBA provides LTRIM and RTRIM functions as well in VBA. If you want to delete the undesired spaces from the left of the string use LTRIM function and to delete unwanted spaces from right side if the string suing the RTRIM function.

Syntax

Parameter

String (Required): This parameter represents the string from which you want to remove the extra leading, trailing and in-between space characters.

Return

The VBA TRIM function return the string after removing the leading, trailing, and in-between space characters from the specified string.

Points to Remember

• The VBA Trim & Worksheet Trim syntax is exactly the same.
• It can only remove leading & trailing spaces, not in-between spaces.
• We can trim huge sets of data in one shot without checking the number of extra spaces the cells have.
• There are very less or no chances that any extra space will get spared.

# TRIM Example 1: Eliminate the extra space characters from the String ” Hello, Hope you are enjoying this tutorial? “

Excel VBA Trim Function is used for removing the unwanted space character from any cell or text and returns a string value that has standard spaces.

Following are the steps to write a VBA macro to eliminate the unwanted space characters from the given string using the VBA TRIM function:

Step 1: Open the VBA developer tab. Go to Excel worksheet, from the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: The Moule window will be inserted. Start the program with the macro name followed by the declaration of the variable.

Refer to the following macro code:

Step 4: Next, we will specify the TRIM function and pass the string we want to TRIM. Store the output of the TRIM function in the variable. Once done, we will display the result using VBA MsgBox.

Output

Run the macro either by clicking on the Run option from the VBA ribbon toolbar or pressing the F5 key. You will notice Excel will immediately display the MsgBox.

In the above output, you can see all the leading and trailing spaces have been removed from the string.

# TRIM Example 2: Eliminate the extra space characters from selected Excel cells using VBA button.

The good part of VBA language is that it can be written more than one way. You can assign the macro to your Active button, and it will perform the macro operation whenever the event is clicked. In this example, we will add a customized message using the VBA MsgBox, so it displays a popup message once the data is trimmed.

Following are the steps to write a VBA macro to eliminate the unwanted space characters from the given string using the VBA TRIM function:

Step 1: Open the Excel worksheet. From the ribbon tab click on developer window -> visual basic editor or directly click on the shortcut keywords Alt +F11.

Step 2: The VB Editor window will be displayed. The next step is to create a module. From the ribbon tab click on Insert-> Click on Module.

Step 3: The Moule window will be inserted. Start the program with the macro name followed by the declaration of the variable. Set the variable value with the Excel selection.

Refer to the following macro code:

Step 4: Since, we have to iterate the function to further cells, so will use the For loop. Inside that will trim the string and store the value in Excel cell. Once done, we will display a message note using VBA MsgBox.

Step 5: Once we are done with code writing, we will close the VBA editor window. Again, go to your Excel worksheet click on the Developer -> Insert menu.

As shown below, you will get a drop-down menu displaying Form Controls and ActiveX Controls. Choose the button option from the list.

Step 6: Using your mouse cursor, draw the button shape anywhere on your Excel worksheet. Assign a macro name, and you can also select from the list of created macros. Here we have selected Trim_Function_Example2. Click on OK.

As a result, the button will be created at the selected position of your Excel worksheet. You can also change the name by double-clicking on the top of it. Here, we have mentioned the TRIM function.

Step 7: The last step is to assign some macro to this button. Right-click on the TRIM button. A window will appear displaying the available macros. Select the Marco with the name ‘TRIM_Function_Example1’ and click on OK. Once done, Excel will assign the macro to this button.

Output

We will insert some text strings in various cells to test the above macro with extra space characters.

For testing, select the cells with data and then click on the TRIM Button.

As a result, you will notice all the extra unwanted space characters have been removed from the selected data and it Excel will throw the message as well of “The text is trimmed!”.

Eureka! We have successfully covered various examples, and now you can trim your text and easily get rid of unwanted space characters from your Strings. Go ahead and give it a try and share professional sheets with your colleagues.