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. In this article, we will demonstrate the complete idea of how the VBA Trim function works in Excel independently and then will show 5 relatable examples to use this function efficiently in your work.
Introduction to VBA TRIM Function
Summary
The Trim function in VBA removes the leading and trailing spaces from a supplied text string.
Syntax
Trim(string)
Arguments
| Argument | Required/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.
5 Examples of Using VBA Trim Function in Excel
For ease of understanding, we are going to use a Details of Employee of a certain organization. This dataset includes the ID, Name, Phone Number and their corresponding Addresses in columns B, C, D, and E respectively.
The text in the Name column is aligned to the left in this case. But there are some leading spaces in these cells. Now, we’ll use the VBA Trim function to clean up those extra spaces.
Example 01: Removing Spaces from Specific Text
In the first method, we’ll use the VBA Trim and MsgBox functions to remove spaces from selected texts and show the cleaned data in a pop-up message box. So, let’s go through the example step by step.
- At the very beginning, select cells in the C5:C12 range.
- Then, right-click on the sheet name “Message Box”.
- In the context menu, select View Code from the options.
Immediately, the Microsoft Visual Basic for Applications window appears before us. Also, we can see a code module opened for this sheet.
- After that, paste the following code into the module.
Sub Name_Trim()
Dim Selected_Range As Range, X As String
Set Selected_Range = Selection
For Each cell In Selected_Range
X = Trim(cell)
MsgBox X
Next
End Sub
Immediately, it returns us to the worksheet. Also, Excel shows a message box with the name without any leading space.
- Presently, click OK.
Successively, other cells’ values will be shown without any leading spaces in the message box.
Read More: How to Use VBA Space Function in Excel
Example 02: Removing Spaces in Excel Cells
In this section, we’ll imply the VBA Trim function to remove spaces from selected texts and exhibit the modified values in the same cell. So, allow me to demonstrate the process below.
- First of all, select all cells in the C5:C12 range.
- After that, go to the Developer tab (If you don’t see that in the Ribbon, you have to enable the Developer tab).
- Then, select Visual Basic on the Code group of commands. Alternatively, you can press ALT + F11 to replicate the task.
Similarly, you will find the code module.
Sub EmName_Trim()
Dim Sel_Range As Range
Set Sel_Range = Selection
For Each cell In Sel_Range
cell.Value = Trim(cell)
Next
End Sub
Again, Run this code like the previous one.
Instantly, you can see all the texts in the Name column are free of the leading spaces as they all are left aligned now.
Example 03: VBA Trim Function with Button
The previous example process can be done by using a Button. And there will be a VBA code behind the Button. And so, let’s observe the process.
📌 Steps:
- At first, proceed to the Developer tab.
- Secondly, click on the Insert drop-down on the Controls group.
- Under the Form Controls section, select Button.
- Then, you can place, resize and name the Button as per your need. It’s so simple therefore we’re skipping that part. We named it Trim Name.
- Following this, right-click on the Button.
- From the context menu, click on Assign Macro.
Suddenly, the Assign Macro dialog box appears.
- Then select the previous macro as we created in Example 02.
- As usual, click OK.
- Then, select the desired portion of your dataset. In this case, we selected cells in the C5:C8 range.
- Following this, click on the Trim Name Button.
We can see the changed values in that range.
Example 04: Removing Leading and Trailing Spaces Simultaneously
In the previous examples, we just talked about leading blank spaces and neglecting the trailing spaces. Now, we’ll irradiate removing the trailing spaces. First, we should confirm that there are spaces after the texts. To do this,
📌 Steps:
- Firstly, select the range (C5:C12) of cells.
- Also, jump to the Home tab.
- Consequently, click on Align Right on the Alignment group.
We clearly managed to detect the trailing spaces in cells C5, C6, C10, and C12.
- Then, repeat the previous steps and employ the same code to this dataset.
- After that, select cells in the C5:C12 range.
- Equivalently, Run the macro and you will see the change in the dataset immediately.
You can also double-check the leading spaces by Align Left command.
Example 05: Generating Email ID by Taking Username as Input
Now let’s see how we can easily generate emails by taking usernames as input. Here our task is to take input names and return a structured email address using those names.
📌 Steps:
- Primarily, bring the code module like before.
- Therefore, paste the following code into the module.
Sub Email_Generate()
Dim FN As String, Ln As String
FN = InputBox("Enter your first name")
Ln = InputBox("Enter your last name")
MsgBox ("Your email is: " + Trim(LCase(FN)) + Trim(LCase(Ln)) + "@xyz.com")
End Sub
- Thus, Run the code.
Abruptly, it will get you back to the worksheet and show you an Input Box.
- Then enter your first name in the box.
- Afterward, click OK or press ENTER.
Then, another Input Box will show up.
- In this box, give the last part of your name.
- As always, click OK.
On the spur of the moment, you will get an output containing the email address, including your name.
Difference Between Excel TRIM & VBA Trim Function
- Excel TRIM function can remove in-between spaces. That means any spaces between two words can be eliminated by this function.
- VBA Trim function can only remove leading and trailing spaces. Like Excel’s TRIM function, it cannot remove spaces in between.
Issues with Trim function in VBA
VBA Trim function works for most cases, but e.g. if I use this code on digits with spaces to the right “14555557899200155 ”, using the VBA Trim function will remove the trailing spaces but there will be a change in the value, like “14555557899200155” 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.
What to Do If VBA Trim Function Is Not Working
A common error happens with this function, i.e. it doesn’t show the output all the time. The remedy for this problem is:
- Make sure to compile the code before assigning it to any button.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
To conclude, we have tried to give a summary of the VBA Trim function and its different applications. We have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.
Related Articles
- How to Use VBA SPLIT Function in Excel
- Call a Sub in VBA in Excel
- How to Create a Body Mass Index Calculator in Excel Using VBA
- Use VBA Asc Function
- How to Use VBA Function Procedure with Arguments in Excel
- Create Custom VBA functions and Use them in the Worksheet
- How to Use VBA Large Function in Excel
