Trim and Len

These two string functions are not related, but they are easy enough to use. We'll use them together in this next example.

The Trim function is used to trim unwanted white space for text. So if you had the following string:

"   some text   "

Using Trim on it would remove the spaces to leave this:

"some text"

The Len function is used to get how many characters a string has.

Create another Sub in your code window. Call it TrimAndLen . Add the following code:

Dim FullName As String
Dim LengthFullName As Integer

FullName = "   David Gilmour   "

LengthFullName = Len(FullName)

MsgBox LengthFullName

We've set up two variables here, one called FullName and one called LengthFullName . The LengthFullName variable has been set up as an Integer. Into the variable called FullName we've stored the text "   David Gilmour   " . But notice where the double quotes are. We have three blank spaces to the left of the name and three blank spaces to the right of the name.

The fourth line is this:

LengthFullName = Len(FullName)

We're using the Len function to the right of an equal sign. In between the round brackets of Len, we have our FullName variable. The Len function will count how many characters are in the text that we've stored inside of FullName . When VBA has an answer to the Len function it stores it into the variable called LengthFullName . Because the Len function counts characters, the value returned will be an Integer.

Run the code and you'll find that the message box displays the number 19.

However, the name David Gilmour is only 12 characters long. Add the space and it 13 characters. The message box is displaying 19 because it has counted the extra space at the beginning and the end.

To remove the space, use the Trim function:

FullName = Trim("   David Gilmour   ")

The variable or direct text you're trying to trim goes between round brackets. VBA will then remove any white space from the front and the end of your string.

Run the code again and the message box displays a value of 13.