Home and Learn - Free Excel VBA Course


4.8 An Excel VBA String Method Exercise

To get some practice with String methods, we're going to work through a problem. Try to solve the problem yourself before going through the solution below. OK, here's the problem.

Problem

Suppose you had a product code on a spreadsheet that looked like this:

PD-23-23-45

However, it's in the wrong format. First, you're told to remove all the hyphens. Then, you're told to turn the letters PD in the product code to PDC. So your finished work should look like this:

PDC232345

The question is, how would you do this with Excel VBA code?

If you get stuck solving the problem above, then here's the solution.

 

Solution

The first part of the problem, removing the hyphens, is fairly easy - just use Replace:

Dim ProductCode As String
ProductCode = "PD-23-2345"
ProductCode = Replace(ProductCode, "-", "")

In between the round brackets of Replace we have the text we want to search, which is the variable called ProductCode. After a comma, we have the character we want to replace, the hyphen. Next, we have the new character, which is no text at all. This is done with two double quotes with no space between them.

The second part of the problem, adding the "C" after "PD", would be easy too, if VBA had an Insert function. But it doesn't. Which makes this part of the problem a little bit harder.

There are a few ways to insert the "C" in the correct place. We'll do it using the Left and Mid string functions. We'll use Left to grab the first two characters, then add the "C". We'll then use Mid to get the numbers.

To get the first two characters and add the "C", the code is this:

Dim Letters As String
Letters = Left(ProductCode, 2) & "C"

Starting from the left of ProductCode, we grab two characters:

Left(ProductCode, 2)

The "C" is added with concatenation:

Left(ProductCode, 2) & "C"

The new three letter code is then stored in the variable called Letters.

To get the numbers, use Mid:

Dim Numbers As String
Numbers = Mid(ProductCode, 3)

Mid first needs the string you're searching, which is ProductCode for us. Next, we have 3 as the starting point to grab characters. This will grab the characters starting from the 3rd character in the string. Because we haven't specified an end number, Mid will grab the rest of the characters to the end of the string.

The only thing left to do is to join the two parts together:

Dim NewCode As String
NewCode = Letters & Numbers
MsgBox NewCode

This just uses concatenation to join the Letters variable to the Numbers variable. The final line uses a message box to display the results. The whole of the code, though, looks like this:

Solution to the String exercise

 

When you meet a problem like the one above, the solution is usually to use one of the Left, Right, or Mid functions (or all of them) to chop the string into pieces and then join them back together again. In the next lesson, we'll move on and tackle programming loops.

< Mid

Next Lesson: 5.1 Loops in Excel VBA >