How to Use TRIM Function in VBA in Excel (Definition + VBA Code)

Overview of VBA TRIM Function

Data cleaning is an important part of data analysis. In Excel, TRIM is another most usable function in VBA code which removes extra spaces from the string or text. This will help to prepare the dataset more efficiently. This article will share the complete idea of how the VBA TRIM function works in Excel independently and then with other Excel functions.

Download the Practice Workbook

VBA TRIM Function in Excel (Quick View)

Overview of VBA TRIM Function

VBA TRIM Function: Syntax & Arguments

 VBA TRIM Function syntax and argument

 

Summary

The TRIM function in VBA removes the leading and trailing spaces from a supplied text string.

Syntax

Trim(string)

Arguments

Argument Required or Optional Value
string Required This is the argument that can be a text string or a variable that holds a text string.

Note:

  • The string could be a cell reference as well as a direct supply of value to the formula.
  • This function helps to remove Leading Spaces, Trailing Spaces, but not in-between spaces.
  • Always save the file as a Macro-Enabled Worksheet. By doing this, we can use the assigned macro multiple times.

How to Use the TRIM Function in VBA Excel (3 Examples)

Example 1: Showing the Result in the Same Column Using VBA TRIM Function

Let’s assume we have a dataset of some customers with their ID, Name, Phone Number, Address. In the Name section, there are some extra spaces. Now our task is to clean those extra spaces and show the cleaned data in a pop-up message box.

Showing the Result in a Message Box Using VBA TRIM Function

Step 1: Select the data that you want to trim. In my case, it’s the Name column

Select name column

Step 2: Under the Developer tab, select the Macros option (Shortcut Alt + F8)

 Open macros

Step 3: Now there will be a popup window, you must write the macro file and click on create option (Make sure that This Workbook is selected under Macros in option.)

 create marcro file

Step 4: Now enter the code in the VBA window and Run it (Shortcut F5)

Enter vba code

Code:

Sub Name_TRIM()
 
    Dim MyRange As Range

    Set MyRange = Selection

    For Each cell In MyRange
     cell.Value = Trim(cell)
    Next
 
End Sub

Step 5: Now see the output on the same column

Output of VBA TRIM Function

Example 2: Showing the Result Using Button 

The previous example process can be done by using a button. And there will be a VBA code behind the button. Let’s see the process.

Step 1: Under the Developer tab, select the Button option from the Controls section

Select button

Step 2: You can name the Button as per your need

Name the button

Step 3: Now Right-Click on the button and select the Assign Macros option

Assign macro on button

Step 4: Now we will select the code which we have applied for the previous example then press the OK button

Select previous macro

Step 5: After that select the Name column and click on the button. The values will be changed

Output of VBA button

Example 3: Automatic Generate Email ID by Taking Username as Input

Now let’s see how we can easily generate email by taking usernames as input. Here our task is to take input names and return an email address using names.

Step 1: Go to the Macros option and name your program. For me, I have declared it as Email_Generate and click on the Create button

Generate email macro name

Step 2: Now write the code and run it

Enter name

Code:

Sub Email_Generate()

 Dim name As String, address As String
 IB = InputBox("Enter your first name")
 Lst_IB = InputBox("Enter your last name")
 MsgBox ("Your email is: " + Trim(LCase(IB)) + Trim(LCase(Lst_IB)) + "@xyz.com")
 
End Sub

Code Explanation

  • Dim name As String, address As String this declaring name and address as string data type.
  • IB = InputBox(“Enter your first name”) We are taking the first name as input into the IB variable using this code.
  • Lst_IB = InputBox(“Enter your last name”) using this code we are taking the last name as input into the Lst_IB variable.
  • Trim(LCase(IB)) and Trim(LCase(Lst_IB)) are removing spaces and converting the names into lowercase.

Email generate VBA code

Step 3: Now a window will be pop up and you must enter your name first and last name

Enter first name and last name

Step 4: After press, the OK button the auto-generated email will be shown in the window

Auto generated email

Difference Between Excel & VBA Trim Function

Formula TRIM Function VBA Trim Function
This function can remove in-between spaces. That means any spaces between two words can be eliminated by this function.

Like

Formula TRIM Function

This function can only remove leading and trailing spaces. Like the formula TRIM function, it cannot remove spaces in-between.

Issues with Trim function in VBA

Issue

This works for most cases but e.g., if I use this code on digits with spaces to the right “14555557899200155    “, Using we will use the VBA TRIM function it will remove the trailing spaces but there will be a change in the value like14555557899200155 will become -> 14555557899200100. So, the last two digits are replaced with two zeros.

Solution

Excel has an accuracy of 15 digits. The number 14555557899200155 has a length of 17 digits which means you will lose the last two digits when you place this into an Excel sheet.

Things to Remember

Common Errors When they show
Output not showing Compile the code before assigning it to any button.

Conclusion

To conclude, I have tried to give a summary of the VBA TRIM function and its different applications. I have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. That’s it all about the TRIM function. If you have any inquiries or feedback, please let us know in the comment section.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo