Article updated on April 16, 2018

The picture above shows a user-defined function (UDF) that splits the string in cell B3 using delimiting character "|".

The SPLIT function is a function you can use in Visual Basic for Applications, abbreviation VBA.

It returns a one-dimensional array containing substrings from a divided string based on a delimiting character.

The returning array is zero-based meaning the first substring starts with index 0 (zero), example below.

Array(0) = "a"
Array(1) = "b"
Array(2) = "c"
Array(3) = "d"

Excel VBA Function Syntax

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


expression Required. A string you want to split.
[delimiter] Optional. The delimiting character, default value is " ", in other words, a space character.
[limit] Optional. How many substrings to be returned, default value is all substrings.
[compare] Optional. A numeric value determining how Excel compares when processing.

The following table shows the numeric values you can use in the compare parameter above.

Constant Value Description
vbUseCompareOption -1 Comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Binary comparison
vbTextCompare 1 Textual comparison
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.

The following UDF is demonstrated in the first picture above in cell C3, it uses "|" to split the string in cell B3. The substrings are then concatenated with a space character between substrings. The returning value is shown in cell C3.

Function SplitValues(rng As Range)
Dim Arr() As String
Dim Str As Variant
Dim result As String

Arr = Split(rng, "|")

For Each Str In Arr
    result = result & Str & " "

SplitValues = Trim(result)

End Function

The second UDF is shown in cell C4, it does the same thing as the first UDF above except it returns the last substrings first, in other words, substrings are concatenated backwards.

Function SplitValues1(rng As Range)
Dim Arr() As String
Dim Str As Variant
Dim result As String
Dim i As Integer

Arr = Split(rng, "|")

For i = UBound(Arr) To 0 Step -1
    result = result & Arr(i) & " "
Next i

SplitValues1 = Trim(result)

End Function

Download Excel *.xlsm file

How to use the SPLIT function VBA.xlsm