Parsing a Comma-Separated List

Reference > Science > Technology > Beginner Programming Tips

So, you have a string variable, and it contains a list of values that you want to "extract," and then perform some action on those values? How do you go about doing that? To do it, you'll need three very common string functions: Instr, Mid, and Right. If you don't know what these functions do, you can keep reading below; otherwise, you can skip to the section titled "Putting It Together."

Instr

This function lets you search a string to find another string inside of it. You tell the function what your string is, where in the string to start searching, and what to search for. For example, the following code searches MyString, starting at the tenth character, for the string "hello." If the string "hello" appears in the string, Instr returns the location of the string within the larger string. If it doesn't it returns zero.

C = Instr(10, MyString, "hello")

Mid

This function returns a chunk out of the middle of a string. You tell the function what string to use, the starting character, and the length of the string you want. The following code returns a string out of MyString, five characters long, starting at the tenth character.

NewString= Mid(MyString, 10, 5)

Right

This function is similar to to "Mid", except that it returns a value from the end of a string instead of the middle. So instead of specifying a beginning point and a length, you just have to specify a length. The following code returns the last seven characters of MyString.

NewString= Right(MyString, 7)

Putting It Together

Now let's say that the string MyString contains the following: "1,2,3,4,5," and you want to search out each of the comma separated values and do something with each one of them. The code below does the job:

Dim MyString As String
Dim MyValue As String

Dim C1 As Integer
Dim C2 As Integer

C2 = InStr(C1 + 1, MyString, ",")
While C2 > 0
   MyValue = Mid(MyString, C1 + 1, C2 - C1 - 1)
   'Insert Code to do something with MyValue

   C1 = C2
   C2 = InStr(C1 + 1, MyString, ",")
Wend

MyValue = Right(MyString, Len(MyString) - C1)
'Insert Code to do something with MyValue

So, what's going on here? Well, C1 represents the location of the comma before a value, and C2 represents the location of the comma after a value. Notice that at the beginning, C1 = 0, which is actually not even a valid index into the string (the string starts at the first character!), but that's okay, because if there was a comma before the very first value, it would be at position zero!

So we use Instr to find the next comma, then we use Mid to snag the value between the two commas. Look at this very carefully to make sure you understand why I used "C1 + 1" as the starting point, and "C2 - C1 - 1" as the length.

After extracting a value, we set C1 to equal C2. Why? Because when we go back for the next value, the comma at C2 is now the comma before the next value. So we look for the next comma, and then loop back again and again, until C2 = 0 (which means there are no more commas).

The important thing you cannot forget is this: Since the string doesn't end with a comma, you haven't pulled out the very last value. So that last line of code is necessary to get the last value. Then of course you have to perform your action on that value as well.

Or is that last section really necessary? Check out this piece of code, which saves you the trouble of having to search out that last value:

Dim MyString As String
Dim MyString2 As String
Dim MyValue As String

Dim C1 As Integer
Dim C2 As Integer

MyString2 = MyString & ","
C2 = InStr(C1 + 1, MyString2, ",")
While C2 > 0
   MyValue = Mid(MyString2, C1 + 1, C2 - C1 - 1)
   'Insert Code to do something with MyValue

   C1 = C2
   C2 = InStr(C1 + 1, MyString2, ",")
Wend

Interesting, eh? I created a new string, based on the original string, but having a comma tacked onto the end. So now, when C2 is zero, we really are done!

Questions

How would your code change if each value was separated by a semicolon instead of a comma?

How would your code change if each value was separated by a carriage return? (A carriage return is made up of two characters: character code 13 and character code 10.)

Suppose the list looked like this: "1, 2, 3" (notice there is an extra space after each comma). How does this change your code?