Excel VBA Split Function – Explained with Examples

When working with VBA in Excel, you may have a need to split a string into different parts based on a delimiter.

For example, if you have an address, you can use the VBA Split function to get different parts of the address that are separated by a comma (which would be the delimiter in this case).

SPLIT is an inbuilt string function in Excel VBA that you can use to split a text string based on the delimiter.

This Tutorial Covers:

VBA SPLIT Function – Syntax

Split ( Expression, [Delimiter], [Limit], [Compare] )

• Expression: This is the string that you want to split based on the delimiter. For example, in case of the address example, the entire address would be the ‘expression’. In case this is a zero-length string (“”) SPLIT function would return an empty array.
• Delimiter: This is an optional argument. This is the delimiter that is used to split the ‘Expression’ argument. In case of our address example, a comma is a delimiter that is used to split the address into different parts. If you don’t specify this argument, a space character is considered the default delimiter. In case you give a zero-length string (“”), the entire ‘Expression’ string is returned by the function.
• Limit: This is an optional argument. Here you specify the total number of substrings that you want to return. For example, if you only want to return the first three substrings from the ‘Expression’ argument, this would be 3. If you don’t specify this argument, the default is -1, which returns all the substrings.
• Compare: This is an optional argument. Here you specify the type of comparison you want the SPLIT function to perform when evaluating the substrings. The following options are available:
  • When Compare is 0: This is a Binary comparison. This means that if your delimiter is a text string (let’s say ABC), then this would be case-sensitive. ‘ABC’ would not be equal to ‘abc’.
  • When Compare is 1: This is a Text comparison. This means that if your delimiter is a text string (let’s say ABC), then even if you have ‘abc’ in the ‘Expression’ string, it would be considered as a delimiter.

Now that we have covered the basics of the SPLIT function, let’s see a few practical examples.

Example 1 – Split the Words in a Sentence

Suppose I have the text – “The Quick Brown Fox Jumps Over The Lazy Dog”.

I can use the SPLIT function to get each word of this sentence into as a separate item in an array.

The below code would to this:

Sub SplitWords()
Dim TextStrng As String
Dim Result() As String
TextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"
Result() = Split(TextStrng)
End Sub

While the code does nothing useful, it will help you understand what the Split function in VBA does.

Split function splits the text string and assigns each word to the Result array.

So in this case:

• Result(0) stores the value “The”
• Result(1) stores the value “Quick”
• Result(2) stores the value “Brown” and so on.

In this example, we have only specified the first argument – which is the text to be split. Since no delimiter has been specified, it takes space character as the default delimiter.

Important Note:

1. VBA SPLIT function returns an array that starts from base 0.
2. When the result of the SPLIT function is assigned to an array, that array must be declared as a String data type. If you declare it as a Variant data type, it will show a type mismatch error).  In the example above, note that I have declared Result() as a String data type.

Example 2 – Count the Number of Words in a Sentence

You can use the SPLIT function to get the total number of words in a sentence. The trick here is to count the number of elements in the array that you get when you split the text.

The below code would show a message box with the word count:

Sub WordCount()
Dim TextStrng As String
Dim WordCount As Integer
Dim Result() As String
TextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"
Result = Split(TextStrng)
WordCount = UBound(Result()) + 1
MsgBox "The Word Count is " & WordCount
End Sub

In this case, UBound function tells us the upper bound of the array (i.e., the maximum number of elements the array has). Since the base of the array is 0, 1 is added to get the total word count.

You can use a similar code to create a custom function in VBA that will take the text as input and return the word count.

The below code will create this function:

Function WordCount(CellRef As Range)
Dim TextStrng As String
Dim Result() As String
TextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"
Result = Split(TextStrng)
WordCount = UBound(Result()) + 1
End Function

Once created, you can use the WordCount function just like any other regular function.

This function also handles leading, trailing and double spaces.

Example 3 – Using a Delimiter Other than Space Character

In the previous two example, we have only used one argument in the SPLIT function, and rest were the default arguments.

When you use some other delimiter, you need to specify that in the SPLIT formula.

In the below code, the SPLIT function returns an array based on a comma as the delimiter, and then shows a message with each word in a separate line.

Sub CommaSeparator()
Dim TextStrng As String
Dim Result() As String
Dim DisplayText As String
TextStrng = "The,Quick,Brown,Fox,Jump,Over,The,Lazy,Dog"
Result = Split(TextStrng, ",")
For i = LBound(Result()) To UBound(Result())
DisplayText = DisplayText & Result(i) & vbNewLine
Next i
MsgBox DisplayText
End Sub

In the above code, I have used the For Next loop to go through each element of the ‘Result’ array assign it to the ‘DisplayText’ variable.

Example 4 – Divide an Address into three parts

With the SPLIT function, you can specify how many numbers of splits you want to get. For example, if I don’t specify anything, every instance of the delimiter would be used to split the string.

But if I specify 3 as the limit, then the string will be split into three parts only.

For example, if I have the following address:

2703 Winifred Way, Indianapolis, Indiana, 46204

I can use the Split function in VBA to divide this address into three parts.

It splits the first two based on the comma delimiter and remaining part becomes the third element of the array.

The below code would show the address in three different lines in a message box:

Sub CommaSeparator()
Dim TextStrng As String
Dim Result() As String
Dim DisplayText As String
TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204"
Result = Split(TextStrng, ",", 3)
For i = LBound(Result()) To UBound(Result())
DisplayText = DisplayText & Result(i) & vbNewLine
Next i
MsgBox DisplayText
End Sub

A practical use of this could be when you want to divide a single line address into the format shown in the message box. Then you can create a custom function that returns the address divided into three parts (with each part in a new line).

The following code would do this:

Function ThreePartAddress(cellRef As Range)
Dim TextStrng As String
Dim Result() As String
Dim DisplayText As String
Result = Split(cellRef, ",", 3)
For i = LBound(Result()) To UBound(Result())
DisplayText = DisplayText & Trim(Result(i)) & vbNewLine
Next i
ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1)
End Function

Once you have this code in the module, you can use the function (ThreePartAddress) in the workbook just like any other Excel function.

This function takes one argument – the cell reference that has the address.

Example 5 – Get the City Name from the Address

With Split function in VBA, you can specify what part of the resulting array you want to use.

For example, suppose I am splitting the following address based on the comma as the delimiter:

2703 Winifred Way, Indianapolis, Indiana, 46204

The resulting array would look something as shown below:

{“2703 Winifred Way”, “Indianapolis”, “Indiana”, “46204”}

Since this is an array, I can choose to display or return a specific part of this array.

Below is a code for a custom function, where you can specify a number and it will return that element from the array. For example, if I want the state name, I can specify 3 (as it’s the third element in the array).

Function ReturnNthElement(CellRef As Range, ElementNumber As Integer)
Dim Result() As String
Result = Split(CellRef, ",")
ReturnNthElement = Result(ElementNumber - 1)
End Function

The above function takes two arguments, the cell reference that has the address and the element number you want to return. The Split function splits the address elements and assigns it to the Result variable.

Then it returns the element number that you specified as the second argument. Note that since the base is 0, ElementNumber-1 is used to return the correct part of the address.

In case you want the city name, you can use 2 as the second argument. In case you use a number that is higher than the total number of elements, it would return the #VALUE! error.

You can further simplify the code as shown below:

Function ReturnNthElement(CellRef As Range, ElementNumber As Integer)
ReturnNthElement = Split(CellRef, ",")(ElementNumber - 1)
End Function

In the above code, instead of using the Result variable, it only returns the specified element number.

So if you have Split(“Good Morning”)(0), it would only return the first element, which is “Good”.

Similarly, in the above code, it only returns the specified element number.