The picture above shows a user-defined function (UDF) that splits the string in cell C3 using a delimiting character ",". It then returns an array of values to cell range D3:F3.

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"

1. SPLIT Function Syntax - VBA

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

2. SPLIT Function Arguments - VBA

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

3. Example 1

The following UDF is demonstrated in the 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.

'Name User Defined Function and arguments
Function SplitValues(rng As Range)

'Dimension variables and declare data types
Dim Arr() As String
Dim Str As Variant
Dim result As String

'Split value in variable Rng using delimiting character |
Arr = Split(rng, "|")

'Iterate through each substring
For Each Str In Arr

    'Concatenate each substring and save to variable result
    result = result & Str & " "
Next

'Return variable result to User Defined Function on worksheet
SplitValues = Trim(result)

End Function

Back to top

4. Example 2

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 backward.

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

Back to top

5. Split cell value using a delimiting character

The image above demonstrates a UDF in cell C1 that splits the cell value in cell A1 to substrings using a delimiting character.

The first argument is the delimiting character or characters, the second argument is a cell reference to a cell. The UDF is entered as an array formula if you use an Excel version that is older than Excel 365.

'Name User Defined Function and arguments
Function SplitValues(a As String, b As String)

'Dimension variables and declare data types
Dim Text() As String

'Split variable b using variable a as the delimiting character, save to variable Text
Text = Split(b, a)

'Return output stored in variable Text to User Defined Function
SplitValues = Text
End Function

Back to top

3.1 How to use it

1. Select cell range C1:E1.
2. Type =SplitValues(",",A1) in the formula bar.
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys.

If you did this right, the formula has now a leading { and an ending }, like this {=SplitValues(",",A1)}. They appear automatically, don't enter these characters yourself.

Back to top

3.2 Explaining the user-defined function

Function name and arguments

A user defined function procedure always start with "Function" and then a name. This udf has two arguments, a and b. Both a and b are strings.

Function SplitValues(a As String, b As String)

Declaring variables

Dim Text() As String

Text() is a dynamic string array. Read more about Defining data types.

Split function

Text = Split(b, a)

The Split function accepts a text string and returns a zero-based, one-dimensional array containing all sub strings. Split allows you also to specify a delimiting character, default is the space character. The substrings are stored as multiple strings in Text array.

The udf returns the substrings in Text

SplitValues = Text

End a udf

End function

A function procedure ends with the "End function" statement.

Back to top

3.3 Where to put the code?

To build a user-defined function, follow these steps:

1. Press Alt + F11 to open the visual basic editor
2. Press with left mouse button on "Insert" on the menu
3. Press with left mouse button on "Module"
4. Copy the code above and paste it to the code module.

Back to top

Get the Excel file

How-to-use-the-SPLIT-function-VBA.xlsm

SPLIT function examples

Break up values in a cell range into separate cells based on a space character as a delimiter
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]

Count comma separated values
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]

Create new worksheets programmatically based on values in a cell range [VBA]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

Extract text between words [UDF]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]

Fetching values from ThingSpeak programmatically
  Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]

Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Filter unique strings from a cell range
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]

Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]

Find and replace strings in file names, folder name and subfolders
The following two macros FindReplace() and Recursive() let you rename files and folders recursively based on a search string. The […]

How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

How to use the LIKE OPERATOR
The LIKE operator allows you to match a string to a pattern using Excel VBA. The image above demonstrates a […]

How to use the SPLIT function [VBA]
The picture above shows a user-defined function (UDF) that splits the string in cell C3 using a delimiting character ",". […]

List all hyperlinks in worksheet programmatically
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]

Lookup multiple values in one cell
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]

Prepare data for Pivot Table – How to split concatenated values?
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]

Sort values in a cell based on a delimiting character
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]

Split search value using a delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

Working with ARRAY VARIABLES (VBA)
This post will teach you how to work with Excel arrays in visual basic for applications (VBA). Why do you […]

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.