MS-Excel / Functions and Formula

---

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)