VBA TRIM

What is Excel VBA TRIM Function?

The Excel VBA TRIM function is a built-in function removes leading and trailing spaces from a string. It is like the TRIM function in Excel but is specifically designed for use in VBA code. Let’s look at an example of using the VBA Trim function to clean up user input.

In this example, we have a VBA code snippet demonstrating the TRIM function’s usage to clean up user input. The code starts by declaring two variables: userInput and cleanedInput, both of which are of the String data type.

The user is then prompted to enter a string with leading and trailing spaces using the InputBox function, and their input is stored in the userInput variable.

The TRIM function is applied to the userInput variable, which removes any leading and trailing spaces from the input string. Finally, a message box shows the cleaned input using the MsgBox function.

What Does Trim Function Do?

The TRIM function removes leading and trailing spaces from a string. When applied to a string, the TRIM function scans the characters from both ends and eliminates any spaces encountered until it reaches a non-space character. The resulting string will have all leading and trailing spaces removed.

The primary purpose of the TRIM function is to ensure consistent and clean data processing. Removing leading and trailing spaces helps eliminate any unintentional whitespace that might be present in user input or data retrieved from external sources.

The TRIM function is beneficial when working with user inputs, as it helps normalize the data by removing unnecessary spaces that users might accidentally include while entering information. It is commonly employed when leading or trailing spaces cause data validation or matching issues.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Syntax

The syntax for the Trim function in VBA is as follows:

Trim(string)

Here, “string” is the input parameter representing the string from which you want to remove spaces. This parameter can be any valid expression that evaluates to a string, such as a string variable, a string literal enclosed in double quotes, or the result of a string manipulation operation.

Examples

Let us look at some examples of how to use the VBA TRIM function.

Example #1

Step 1: Open the VBE, press the Alt + F11 keys in Excel or click on the Developer tab in the ribbon and then click on the Visual Basic button.

Step 2: In the VBE, click on the Insert menu and select Module to create a new module.

Step 3: In the new module, start with creating a subroutine “TrimExample1” and declare a variable myString as a String VBA data type. This variable will hold the input string.

Step 4: Next, assign “Hello, World!” to the myString variable. This string contains leading and trailing spaces, i.e., spaces before “Hello” and after the word “World!”

Step 5: Now, use the TRIM function to remove the leading and trailing spaces from the myString variable. The TRIM function is applied to the myString variable, and the result is assigned back to the same variable.

Step 6: In this step, we display the modified string using the MsgBox function. The MsgBox function is used to show a message box with the value of the myString variable, which will be “Hello, World!” without any leading or trailing spaces.

Step 7: Now, save the macro and click on Run. When the code is executed, a message box displays “Hello, World!” output without leading or trailing spaces.

Here is the full code:

Sub TrimExample1()
Dim myString As String
myString = ” Hello, World! “
myString = Trim(myString)
MsgBox myString
End Sub

Example #2

Step 1: Create a subroutine “TrimExample2” in the new module and declare a variable text as a String data type. This variable will hold the input string.

Step 2: Now, we assign the value “Trim Example” to the text variable. This string contains leading and trailing spaces.

Step 3: Next, we use the TRIM function to remove the leading and trailing spaces from the text variable. The TRIM function is applied to the text variable, and the result is assigned back to the same variable.

Step 4: Now, print the modified string, enclosed within square brackets, using the “Debug.Print” statement. The “Debug.Print” statement is used to output the value of the text variable to the Immediate Window of the VBA Editor, surrounded by square brackets. The output will be “[Trim Example]”.

Step 5: Now save the code and click on Run. When the code is executed, the modified string “[Trim Example]” will be printed in the Immediate Window of the VBA Editor.

Here is the complete code:

Sub TrimExample2()
Dim text As String
text = ” Trim Example “
text = Trim(text)
Debug.Print “[” & text & “]”
End Sub

Example #3 – Clean the Data in Cells

Suppose we have data in a range of cells with leading and trailing spaces, as seen in the following image. In this example, we will see how to clean this data using the VBA Trim function.

Step 1: In the new module, start by creating a subroutine “CleanData” and define a range of cells rng (e.g., “A1:A10”) that you want to clean. This range represents the cells in which the data will be cleaned.

Step 2: Loop through each cell within the specified range using a For Each loop. The For Each loop iterates over each cell in the rng range.

Step 3: Within each iteration of the loop, assign the trimmed value of the current cell to itself, using the Trim function and the Value property of the cell. The Trim function is applied to the VBA Value property of the current cell, and the trimmed value is assigned back to the cell.

Step 4: Repeat this process for all cells in the range, removing leading and trailing spaces. The loop ensures the code is executed for each cell in the specified range.

Step 5: Now save the module and click on Alt + F8 to open the macro window.

Step 6: Now select “CleanData” and run the macro. After you execute the macro, the VBA will loop through the range of cells using the VBA Trim function. Once the loop completes, the specified range of cells will contain cleaned data with no leading or trailing spaces.

The data in each cell will be modified in place, with any leading and trailing spaces removed.

Here is the full code:

Sub CleanData()
Dim rng As Range
Dim cell As Range
Set rng = Range(“A1:A10”) ‘ Specify the range of cells
For Each cell In rng
cell.Value = Trim(cell.Value) ‘ Trim the leading and trailing spaces
Next cell
End Sub

Difference Between Excel & VBA Trim Function

The Trim function in Excel and VBA works similarly, removing leading and trailing spaces. However, the key difference lies in their usage.

The Excel TRIM function is used in worksheet formulas, whereas the VBA TRIM function is used within VBA code to manipulate strings programmatically.

Important Things To Note

1. The VBA Trim String only removes leading and trailing spaces; it does not remove spaces within the string.
2. If the input string is empty or consists solely of spaces, the Trim function will return an empty string.
3. The VBA Trim Characters from String precisely target spaces and remove them from a string’s beginning and end. It does not remove any other characters or trim spaces within the string.
4. VBA Trim Spaces primarily removes spaces from a string. It eliminates leading and trailing spaces, ensuring the string remains concise and well-formatted.
5. To remove VBA Trim Leading Zeros from a string, you can use additional string manipulation functions such as the Replace excel function or custom code logic.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA TRIM formulas and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to What is VBA TRIM. We use trim function to remove leading and trailing spaces from string with examples. You can learn more from the following articles –

• VBA Debug Print
• VBA DateDiff Function
• VBA CreateObject