How to Use the VBA Trim Function in Excel

This is an overview.

VBA TRIM Function

The 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	It can be a text string or a variable that holds a text string.

Note:

The string can also be a cell reference.
This function removes Leading Spaces, Trailing Spaces, but not in-between spaces.
Save the file as a Macro-Enabled Worksheet.

This dataset includes Employees’ ID, Name, Phone Number and Addresses.

Examples of Using VBA TRIM Function

There are leading spaces.

Example 1 – Removing Spaces from Specific Text

Steps:

Select C5:C12.
Right-click the sheet name “Message Box”.
Select View Code.

Removing Spaces from Specific Text

The Microsoft Visual Basic for Applications window is displayed.

VBA window appears

Enter the following code in 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

Click the Run icon.

Execute the VBA Code

Excel shows a message box in the worksheet containing the name without any leading space.

Click OK.

Message Box showing result

Other cell values will be shown without leading spaces in the message box.

VBA Trim and MsgBox Function

Example 2 – Removing Spaces in Cells

Steps:

Select C5:C12.
Go to the Developer tab (If you don’t see it on the Ribbon, enable the Developer tab).
Select Visual Basic in Code or press ALT + F11.

Removing Spaces in Excel Cells

Enter the following code in the 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

Run this code following the steps described in the previous Example.

You can see the texts in the Name column without leading spaces. They are left aligned.

VBA Trim function removing spaces from Excel Cell

Example 3 – Using the VBA Trim Function with a Button

Steps:

Go to the Developer tab.
Click Insert in Controls.
In Form Controls, select Button.

Showing Result Using Button

You can place, resize and name the Button. Here, Trim Name.

Creating Button

Right-click the Button.
Click Assign Macro.

Assigning Macro to Button

In the Assign Macro dialog box, select the macro created in Example 2.
Click OK.

Assign Macro dialog box

Selected C5:C8.
Click the Trim Name Button.

Values changed.

Using VBA Trim function with button

Example 4 – Removing Leading and Trailing Spaces Simultaneously

Steps:

Select C5:C12.
Go to the Home tab.
Click Align Right in Alignment.

Removing Leading and Trailing Spaces Simultaneously

Trailing spaces are identified in C5, C6, C10, and C12.

Repeat the previous steps and use the same code.
Select C5:C12.

Run the macro to see the change in the dataset.

Run VBA Code

Double-check the existence of leading spaces by selecting Align Left.

Removing Leading and Trailing Spaces Simultaneously using VBA Trim function

Example 5 – Generating an Email ID by Taking the Username as Input

Generating Email ID by Taking Username as Input

Steps:

Open the code module window.
Enter the following code in 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

Run the code.

Executing the Code

You will see an Input Box in your worksheet.

Enter a name in the box.
Click OK or press ENTER.

Writing name on input box

Another Input Box will be displayed.

Enter the last part of the name.
Click OK.

The output contains an email address, including the provided name.

Using VBA Trim function to generate email id

Difference Between Excel TRIM & VBA Trim Function

Excel TRIM function can remove in-between spaces. Any spaces between two words can be eliminated.
VBA Trim function can only remove leading and trailing spaces.

Issues with the Trim function in VBA

If you use this code for digits with spaces to the right “14555557899200155 ”, the VBA Trim function will remove the trailing spaces but there will be a change in the value: “14555557899200155” will become “14555557899200100”. 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 it into an Excel sheet.