4.3 Trim, Len, and Space in Excel VBA

These three string functions are not related, but they are easy enough to use. We'll use them together in this next example. You can use your Excel workbook and code from the previous section.

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.

Space

You might actually want to pad out a string with blank space. If so, the Space function is the one you want. In between the round brackets, you type a number. This number is how many space characters you want. Here's some code to illustrate this:

Dim FullName As String

FullName = "David Glimour"

MsgBox Len(FullName)

FullName = Space(5) & FullName

MsgBox Len(FullName)

The first message box display a value of 13, which is how many characters are in the name David Gilmour. The second message box displays a value of 18, the 13 original characters, plus 5 added to the start of the name.

We could have added 5 blank spaces to the end of the name with this:

FullName = FullName & Space(5)

You might be confused about the use of the FullName variable twice, here. But start after the equal sign and it will make sense. We have this after the equal sign:

FullName & Space(5)

This says, "Take whatever is in the variable called FullName and join 5 space characters to it." (The & symbol is used to join things together, remember. This is called concatenation.) Once VBA has joined the text and the space, it needs to store it somewhere. Whatever is to the left of the equal sign is the place where it will be stored. To the left of the equal sign, we have the FullName variable again. Whatever was previously in the variable will be replaced. It will be replaced by the value from the right of the equal sign, which was the name plus 5 characters.

In the next lesson, we'll take a look at the Replace function in Excel VBA.

< Change Case

Next Lesson: VBA Replace >

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site