Using LTrim, RTrim, and Trim to Trim Spaces from a String
Often you'll need to trim strings before concatenating them to avoid ending up with extra spaces in inappropriate places, such as in the middle of eight-character filenames. And always remember that users might randomly type spaces in various ways when entering data.
Programs and databases, however, expect a particular format for their data (so the data can easily be searched, sorted, and otherwise manipulated). For example, if 500 users entered their zip code, some might type a space before entering the digits. Any such entries would be placed at the start of a list after it was alphabetically sorted (the space character is seen as "lower" than ordinary characters by a sorting function). It's easy, though, to use the Trim functions to get rid of spaces.
As you saw in Table below, VBA provides three functions specifically for trimming leading spaces and trailing spaces from strings:
- LTrim removes leading spaces from the specified string.
- RTrim removes trailing spaces from the specified string.
- Trim removes both leading and trailing spaces from the specified string.
You can simply use Trim instead of figuring out whether LTrim or RTrim is appropriate for what you expect a variable to contain. At other times, you'll need to remove either leading or trailing spaces while retaining spaces on the other end. In those special cases, you'll need to use either LTrim or RTrim. RTrim is especially useful for working with fixed-length String variables, which will contain trailing spaces if the data assigned to them is shorter than their fixed length.
The syntax for the LTrim, RTrim, and Trim functions is straightforward:
LTrim(string) RTrim(string) Trim(string)
In each case, string is any string expression.
You could use the Trim function to remove both leading and trailing spaces from a string derived from the current selection in the active document in Word. The first line in this next code example declares strUntrimmed and strTrimmed as String variables. The second line assigns the data in the current selection to the strUntrimmed string. The third line assigns the trimmed version of the strUntrimmed string to the strTrimmed string:
Dim strUntrimmed As String, strTrimmed As String strUntrimmed = Selection.Text strTrimmed = Trim(strUntrimmed)
In this tutorial:
- Using Built-in Functions
- What Is a Function?
- Passing Arguments to a Function
- Using Functions to Convert Data from One Type to Another
- Using the Asc Function to Return a Character Code
- Using the Str Function to Convert a Value to a String
- Using the Format Function to Format an Expression
- Using the Chr Function and Constants to Enter Special Characters in a String
- Using Functions to Manipulate Strings
- Using InStr and InStrRev to Find a String within Another String
- Using LTrim, RTrim, and Trim to Trim Spaces from a String
- Using Len to Check the Length of a String
- Using the StrComp Function to Compare Apples to Apples
- Using VBA's Mathematical Functions
- Excel VBA's Date and Time Functions
- Using the DateDiff Function to Return an Interval
- Using File-Management Functions