VBA TRIM Function

Excel is all about manipulating and working the data. These data are usually imported from other database channels or online servers. The common problem many users often face is unorganized data and unwanted spaces in the cell. Dealing with unneeded spaces is very challenging to work with. For such problems, it has introduced an inbuilt function called TRIM. The VBA Excel TRIM function gets rid of “Leading Spaces, Trailing Spaces, and in-between space.” Though Excel worksheet functions also have a TRIM function, which works the same way as VBA TRIM excel as the excel function, there is only one slight difference.

This tutorial will run you through the definition, syntax, parameters, return type, and different examples explaining how to write a macro code using the VBA TRIM function.

What is VBA TRIM Function?

The VBA TRIM function is used to clean the unwanted spaces from your data. It will eliminate all the extra spaces from the characters of your string.

This function is simple and works straightforwardly by taking a string (that you specify to trim) as a parameter. For instance, if you have the string ” Hello, Hope you are enjoying this tutorial ? “. Here, there are unnecessary leading and trailing space characters in the string. You can remove all the spaces by using the VBA TRIM function.

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.

VBA TRIM Function

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.

VBA TRIM Function

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.

VBA TRIM Function

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.

VBA TRIM Function

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.

VBA TRIM Function

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.

VBA TRIM Function

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.

VBA TRIM Function

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.

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

VBA TRIM Function

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

VBA TRIM Function

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.


Next TopicVBA MID Function

Previous articleVBA Data Types: Specifying Variable Types for Data Storage
Next articleUnderstanding Circular References in Excel