Learn about Trim Function

Learn about Trim Function

In this topic we will learn about following topic

1. Introduction of Trim Function 
2. VBA Code for Basic Example
3. VBA code for Loop function in Trim
4. Use of Replace function

Introduction

The Trim function in VBA is used to remove leading and trailing spaces from a string.

It is a simple yet powerful function that ensures data consistency, especially when dealing with user input or imported datasets.

Syntax

Trim(string)

Parameters

string (Required):
The string expression from which you want to remove leading and trailing spaces.

Return

The Trim function returns a string with leading and trailing spaces removed. Spaces within the string are not affected

Examples

1. Basic Example for use of Trim Function

VBA Code:

Sub TrimExample()

Dim Text As String

Dim result As String

' Define a string with leading and trailing spaces

Text = " Hello, Mr.Vijay! "




' Use the Trim function

result = Trim(Text)




' Display the results

MsgBox "Original data:[" & Text & "]" & vbCrLf & "Trimed: [" & result & "]"




End Sub

As a result output massage will display :

Original: [ Hello, Mr.Vijay! ]

Trimmed: [Hello, Mr.Vijay!]

2. Spaces Between Words

In this case we Use replace function, with Trim function we can only remove left and right spaces.

VBA Code:

Sub withcleandata()

Dim text As String

Dim result As String

Dim cleantext As String

text = " Hello, Mr.Vijay! "

cleantext = Replace(text, " ", "")

result = Trim(text)

MsgBox "Original : [" & text & "]" & vbCrLf & "Replaced_text [" & cleantext & "]" & vbCrLf & "Trimed text [" & result & "]"

End Sub

3. Using Trim in a Loop

VBA Code:

Sub TrimInLoop()

Dim cell As Range

For Each cell In Selection

If Not IsEmpty(cell.Value) Then

cell.Value = Replace(cell.Value, " ", "")




End If

Next cell

End Sub

VBA Code Excel for Download

More related topic to Read

1. VBA Split Function : The Split function in VBA is used to break a string into an array of smaller strings, based on a specified delimiter (e.g., a comma, space, or any other character).
2. VBA Loops : In VBA (Visual Basic for Applications), loops are used to repeat a block of code multiple times until a certain condition is met.
3. VBA For Loop : A For Loop in VBA (Visual Basic for Applications) is used to repeat a block of code a specified number of times. There are two main types of For loops in VBA:
4. VBA If Then Else : The If…Then…Else statement in VBA is used to execute different blocks of code based on whether a condition is True or False.