Home / Functions / Split Function

Split Function

The Split function is a VBA function that is used for breaking strings into an array. The separation of string is identified by indicating a character or set of characters which are called delimiters. This function comes in handy, for example, when you have a list of whole names of people in a column of your worksheet and you want to split them in order to get the first and last names of each person. We will demonstrate this in the last part of this article, but let’s have the overview first on the basic functionality. The syntax is:

Split (expression, [delimiter], [limit], [compare])

It has 4 parameters but only the first one is required. Expression is the text you want to split. The delimiter is the character(s) used to identify the partition of your string expression. Since this is optional, this can be omitted and space character will be used by default. Let’s take a look at the following example:

Dim expression As String
Dim result() As String
Dim i As Integer
 
expression = "This is a Split Function"
delimiter = " "
result = Split(expression, delimiter)
 
For i = 0 To UBound(result)
    Debug.Print result(i)
Next

The function searches through the string expression then if it finds the specified delimiter, it pushes the searched string into the array. The searching continues until the end of the expression. In the example above, the expression is This is a Split function and the delimiter is a space. We can omit the delimiter parameter since the default is also a space character. The code returns:

This
is
a
Split
Function

The third optional parameter is the limit which tells the maximum number of substrings it will return. The default is set to -1, which directs that it will return all the substrings. Let’s modify the example a bit:

Dim expression As String
Dim result() As String
Dim limit As Integer
Dim i As Integer
 
expression = "This is a Split Function"
delimiter = " "
limit = 2
result = Split(expression, delimiter, limit)
 
For i = 0 To UBound(result)
    Debug.Print result(i)
Next

The function will only produce two substrings because we have only set the limit to two. The code returns:

This
is a Split Function

This final parameter is the compare method. It is the type of comparison to perform by the search and can be vbBinaryCompare (case-sensitive), vbTextCompare (case-insensitive), and vbDatabaseCompare (use the settings of the database for comparison). The binary comparison method is used by default so it will only search for the delimiter with the exact value you defined.

Dim expression As String
Dim result() As String
Dim limit As Integer
Dim i As Integer
 
expression = "This is a Split Function"
delimiter = " A "
result = Split(expression, delimiter)
 
For i = 0 To UBound(result)
    Debug.Print result(i)
Next

Since the default vbBinaryCompare method is used, the delimiter which is an uppercase “A” will not be found and therefore will return This is a Split Function with no splitting occurred. If vbTextCompare will be used:

expression = "This is a Split Function"
delimiter = " A "
result = Split(expression, delimiter, , vbTextCompare)

this will return:

This is
Split Function

Let’s now try to use the Split function in an actual worksheet by extracting the first and last name of random people listed in our column below.

list of names

Open the VBA Editor and insert new module. Paste the following codes:

Public Function getFirstName(expression As Range) As String
    Dim result() As String
    result = Split(expression.Value)
    getFirstName = result(0)
End Function
 
Public Function getLastName(expression As Range) As String
    Dim result() As String
    result = Split(expression.Value)
    getLastName = result(1)
End Function

What we have here are basically two functions to display the first and last name which is extracted from the full name in the list. Enter the function in the address bar in B2 to show the first name.

getFirstName

Enter the following in the next cell for the last name:

getLastName

Drag and copy to other cells until the end of the list.

Final result
Final result

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

Retain Trailing Zeros in a Text String

For this request we needed to ensure that a trailing zero was always displayed for a calculated text string. Through the use of an additional function the string included trailing zero's