Learn VBA : String Manipulation

The next article in our line of learn VBA will teach you how to use some of the most common string manipulation commands. These commands are easy to learn and are actually quite common among most programming languages. The commands we will look at are Len for “length”,Mid for “middle” , left & Right and then finally Replace and Split.

First off we will look at len. Len is used to tell us the length or the number of characters that are in the string specified for example

Dim iCount,sWords
sWords = "Hello, how are you today"

iCount = Len(sWords)
Debug.log(iCount)

The value in this case for iCount will be twenty four. The next command we will look at is Mid. Mid is used to take out some contents from a string so for example.

Dim sWords,sWord

sWords = "Hello, how are you today"
sWord = mid(sWords,7,3)
debug.Log(sWord)

would set the value of sWord to be “how”. So, take the string sWords , find the seventh character in the string and then take the next 3 letters. The next two commands we will look at together. The Left and Right commands allow us to play with the end or beginning of a string. For example,

Dim sWords,sLeft,sRight

sWords = "Hello, how are you today"
sLeft = left(sWords,5)
sRight = right(sWords,5)
debug.log(sLeft + ":" + sRight)

So in this case sLeft would have the value Hello and sRight would have the value today. As I hope you can see both the commands take the string value first and then the number of characters you require to return. The next command we will look at is the replace command. As the name suggests it will search for something in a string and replace it with whatever we pass it. So for example

Dim sWords,sLeft,sRight

sWords = "Hello, how are you today"
sWords = Replace(sWords,", "," John, ")
debug.log(sWords)

would take the string sWords look for a comma and a space and then replace it with a space followed by the word John and then a comma and a space. This would produce the following output,

Hello John, how are you today

Finally we will look at the split command. This is a really useful command. Basically it takes a string and splits it on a specified delimiter for example,

Dim sWords,sWord
sWords = "Hello, how are you today"

For Each sWord in split(sWords," ")
	Debug.log(sWord)
Next

Would take the full string and find the first space and then show all the characters up to that , so in this case “Hello,” then it would find the next space, and show us all the characters from the last space to this space and so on until we get the output like this.

Hello,
how
are
you
today

And there you have it, some very easy to use and remember commands that will allow you to manipulate your strings into the format that you require. As always if you have any questions then please feel free to drop us an email or leave a comment and we will get back to you as soon as we can.