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 ",". 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.
Table of Contents
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
| 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. |
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
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
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
3.1 How to use it
- Select cell range C1:E1.
- Type =SplitValues(",",A1) in the formula bar.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- 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.
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.
3.3 Where to put the code?
To build a user-defined function, follow these steps:
- Press Alt + F11 to open the visual basic editor
- Press with left mouse button on "Insert" on the menu
- Press with left mouse button on "Module"
- Copy the code above and paste it to the code module.
SPLIT function examples
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
Blake asks: I have a somewhat related question, if you don't mind: I have very large amount of text in […]
Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
The following two macros FindReplace() and Recursive() let you rename files and folders recursively based on a search string. The […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The LIKE operator allows you to match a string to a pattern using Excel VBA. The image above demonstrates a […]
The picture above shows a user-defined function (UDF) that splits the string in cell C3 using a delimiting character ",". […]
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
This post will teach you how to work with Excel arrays in visual basic for applications (VBA). Why do you […]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form