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)
VBA TRIM Function: Syntax & Arguments
The TRIM function in VBA removes the leading and trailing spaces from a supplied text string.
|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.|
- 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.
Step 1: Select the data that you want to trim. In my case, it’s the Name column
Step 2: Under the Developer tab, select the Macros option (Shortcut Alt + F8)
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.)
Step 4: Now enter the code in the VBA window and Run it (Shortcut F5)
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
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
Step 2: You can name the Button as per your need
Step 3: Now Right-Click on the button and select the Assign Macros option
Step 4: Now we will select the code which we have applied for the previous example then press the OK button
Step 5: After that select the Name column and click on the button. The values will be changed
- Use VBA UCASE function in Excel (4 Examples)
- How to Use MsgBox Function in Excel VBA (A Complete Guideline)
- Use InStr Function in VBA (3 Examples)
- How to Use LCase Function in VBA in Excel (With 4 Examples)
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
Step 2: Now write the code and run it
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
- 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.
Step 3: Now a window will be pop up and you must enter your name first and last name
Step 4: After press, the OK button the auto-generated email will be shown in the window
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.
|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
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.
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.|
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.