Split Function

             Split function is a very useful function in VBA. We can use this function to split a string in to sub-strings. Function will return these sub-strings in an array.

There are four parameters in this function

1. Expression
2. Delimiter
3. Limit
4. Compare

First parameter is required and other three are optional.

I will explain how to use this function by simple examples. Assume that we have a string like "a,b,c,d,e,f,g,h,i,j" and need to split this string at every occurrence of  ","
And then need to store those sub strings in a column.

So first we need to define a string array

Dim WrdArray() As String

This array will store the sub-strings once we split our input string.

And we need to define a string variable to hold our input string.

Dim text_string As String

Then we will assign our input string to that string.

text_string = "a,b,c,d,e,f,g,h,i,j"

After that we can use Split function as follows

WrdArray() = Split(text_string, ",")

Now sub-strings are stored in this WrdArray array. So what we need to do now is retrieve those sub strings from that array and store them in a column.
Below code segment will do that. Sub strings will stored in column A.

For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

Array we get (WrdArray)  is a zero-based, one dimensional array. So LBound(WrdArray) is equal to 0. That's why we used the line Range("A" & i + 1) in above code

And below is the complete VBA code of the above example.

Dim WrdArray() As String Dim text_string As String text_string = "a,b,c,d,e,f,g,h,i,j" WrdArray() = Split(text_string, ",") For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

If you run it, you will get  below result

In above example we used "," as our delimiter. What if we omit the delimiter string

Here is an example.

Dim WrdArray() As String Dim text_string As String text_string = "This is a example string." WrdArray() = Split(text_string) For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

We haven't use any delimiter here. So if we omit the delimiter, Split function will assume Space character (" ")  as delimiter.
So you will get the below result.

Third parameter of Split function is limit. Default value is -1. However if the limit is greater than zero, then the string will be divided in to sub-strings at first (Limit-1) occurrences. So the number of sub strings will be equal to the Limit. Below subroutine will give you clear idea about how it works.

Sub Example_Limit_Parameter() Dim WrdArray() As String Dim text_string As String text_string = "This/is/a/example/string." WrdArray() = Split(text_string, "/", 3) For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i End Sub

Here is the result

Compare is the last parameter of the this function. It defines what kind of comparison should be done by the split function. It can have only two values. Either CompareMethod.Binary (0) or CompareMethod.Text (1).  You can use CompareMethod.Binary for case sensitive comparisons and CompareMethod.Text for case in-sensitive comparisons.