Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VBA Trim Function in Excel (5 Practical Examples)

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.

VBA TRIM Function


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Introduction to VBA TRIM Function

VBA TRIM Function: Syntax & Arguments

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.

Examples of Using VBA TRIM Function

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.

📌 Steps:

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

Removing Spaces from Specific Text

Immediately, the Microsoft Visual Basic for Applications window appears before us. Also, we can see a code module opened for this sheet.

VBA window appears

  • 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

VBA code to trim leading spaces

  • Afterward, execute the code using the play-button-shaped Run icon.

Execute the VBA Code

Immediately, it returns us to the worksheet. Also, Excel shows a message box with the name without any leading space.

  • Presently, click OK.

Message Box showing result

Successively, other cells’ values will be shown without any leading spaces in the message box.

VBA Trim and MsgBox Function

Read More: How to Use VBA Space Function in Excel (3 Examples)


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.

📌 Steps:

  • First of all, select all cells in the C5:C12 range.
  • After that, go to the Developer tab.
  • Then, select Visual Basic on the Code group of commands. Alternatively, you can press ALT + F11 to replicate the task.

Removing Spaces in Excel Cells

Similarly, you will find the code module.

  • Thenceforth, copy the code below and paste it into 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

VBA code to remove spaces in Excel cell

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.

VBA Trim function removing spaces from Excel Cell

Read More: How to Remove Duplicates in Excel Sheet (7 Methods)


Similar Readings


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.

Showing Result Using 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.

Creating Button

  • Following this, right-click on the Button.
  • From the context menu, click on Assign Macro.

Assigning Macro to Button

Suddenly, the Assign Macro dialog box appears.

  • Then select the previous macro as we created in Example 02.
  • As usual, click OK.

Assign Macro dialog box

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

Using VBA Trim function with button


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.

Removing Leading and Trailing Spaces Simultaneously

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.

Run VBA Code

You can also double-check the leading spaces by Align Left command.

Removing Leading and Trailing Spaces Simultaneously using VBA Trim function

Moreover, you can go through the articles How to Use VBA LTrim Function in Excel and How to Use VBA RTrim Function to remove only the leading or trailing blank spaces.


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.

Generating Email ID by Taking Username as Input

📌 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

VBA code to generate email id

  • Thus, Run the code.

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

Writing name on input box

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.

Using VBA Trim function to generate email id


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.

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. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.


Related Articles

Md. Abdullah Al Murad

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