From the course: Excel VBA: Managing Files and Data

Remove spaces from before or after a string - Microsoft Excel Tutorial

From the course: Excel VBA: Managing Files and Data

Start my 1-month free trial

Remove spaces from before or after a string

“

- [Instructor] When you create textual output, such as message boxes using Excel VBA, you will often need to add or remove spaces between the text elements that are passed in. In this movie, I will show you how to do that. My sample file is 04_05 Trimming Text. That's a macro-enabled workbook that you can find in the chapter four folder of the exercise files collection. I'm not going to work with any worksheet data in this workbook. So I'll press alt F11 to move to the visual basic editor. And here I have a sub-routine called Trim Text. At the top I define the variables that I'll be using. And below that I assigned the specific text strings to sText1 through sText3. SText1, as you can see, has a space at the end sText2 has a large number of spaces to the left and right of the text. And sText3 has a statement with no spaces to the left or right. I can concatenate all those values into sTextall, adding a space between each of the variables that are added and then finally display it all in a message box. So let's run this code by pressing F5 and see how it comes out without any modifications. So I'll press F5, and that is a messy dialog box. So I'll go ahead and click okay. And now we can use three functions to change how Excel handles the extra spaces that are added to sText2. Most often, you will want to remove excess spaces from the left and right of a text string. To do that in Excel VBA, you use the trim keyword and its function is very much like that, of the trim function that you use in a formula. So I will click to the left of sText2 in the concatenation where I combine the text elements into sTextall and I will type trim followed by a left parentheses at the start of sText2 and then a right parentheses. So if I did this correctly, what will happen is that Excel VBA will remove all the excess white space from the left and right of the text in sText2. So I'll press F5 to run. And I see that the message box is much, much better, except that it appears that I have an extra space here between a and test. So I'll click okay. And in fact, there is an extra space at the end of sText1. There are a couple of ways that I could handle this situation and remove that space. The first thing that I could do is to edit the string, but that might not always be possible. This is meant to be a simple example, where I give you the string and fixed form. You could also remove the space from the right side of sText1, using the Rtrim keyword. So that is just like trim, except us only on the right side. So I will click to the left of the sText1 variable name in the sTextall assignment line, and I'll type Rtrim. And I will enclose sText1 in parentheses because that's the argument or the value of this being passed to Rtrim. And when I press F5, we see that the space to the right of the letter or the word A is gone and the text looks to be correct. So I click okay and go back. One last keyword that you can use is Ltrim. So just like Rtrim, trims extra space on the right, this will trim extra space on the left. So we'll do one final example. And for this, I will change trim to Ltrim. So it will come out wrong. In other words, we will have extra spaces after the word, how, but this is just to demonstrate how it works. So I press F5 to run. And we see that those extra spaces are there. It's not what we want, but the demonstration was successful. So I click, okay. And we're back in the visual basic editor. Trim, Ltrim and Rtrim are very useful functions. You'll be able to remove excess white space from any strings that are passed to a function or sub-routine that you create inside of your module.

Contents