Excel / VBA Split – Break a String with Examples
The Split function is used to break a given string. It returns an array of substrings.
What is Split function in VBA
- The Split function is used to break a given string.
- It returns an array of substrings.
- A delimiter can be specified e.g. dash, comma, dot. Default is space
- You may also limit the number of splits.
- The returned array is one-dimensional.
- It’s index starts at 0.
- The examples below show its usage in VBA as well as Excel data
Syntax:
Split(expression, [ delimiter, [ limit, [ compare ]]])
An example of VBA Split with expression only
We only passed the required argument in the Split function i.e. string to split.
See the code and output:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub split_ex() Dim str_sp As String Dim arr str_sp = "VBA is Simple and Cool" arr = Split(str_sp) MsgBox (arr(0)) End Sub |
Result:
How did it work?
- The string is passed to the Split function.
- It broke the whole string as we did not limit the number of splits.
- As no delimiter is specified, so string is broken by space.
- In the message box, we specified returned array’s zero element. It’s value “VBA” is displayed.
Displaying all elements of the array returned by Split
This time we displayed all elements of the array in the message box. For that, a For..Each loop is used after splitting the string.
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub split_ex() Dim str_sp As String Dim arr str_sp = "VBA is Simple and Cool" arr = Split(str_sp) For Each Item In arr sp_str = sp_str & vbNewLine & Item Next Item MsgBox (sp_str) End Sub |
Result:
Splitting a string by dash delimiter in an Excel sheet
For this example, we will get the A2 cell text and use it in the Split function.
The text contains dashes. We will break the text by dash delimiter in the Split function and display the split values as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub split_ex() Dim arr arr = Split(Range("A2"), "-") For Each Item In arr sp_str = sp_str & vbNewLine & Item Next Item MsgBox ("Text after Split: " & sp_str) End Sub |
Output:
Limiting the number of splits example
In this example, we used the third argument limit in the Split function.
We will specify the dash as a delimiter and 2 splits. For the text to be broken, we used the same Excel cell as in the above example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub split_ex() Dim arr 'Using split with dash and limit 2 arr = Split(Range("A2"), "-", 2) 'Iterating through returned array For Each Item In arr sp_str = sp_str & vbNewLine & Item Next Item 'display the result MsgBox ("After Split: " & sp_str) End Sub |
Result:
You can see, the string is split into two parts only. The second part has a dash.
Using compare argument in Split
The compare argument allows you to perform a case-insensitive split, if you provide a string as a delimiter.
The default split is case-sensitive.
The compare argument has four possible values:
- vbUseCompareOption
- vbBinaryCompare
- vbTextCompare
- vbDatabaseCompare
By using the third option, vbTextCompare, you may perform case-insensitive split.
To understand, consider this string:
str = “This is a car. this is a toy. This is good. this is bad. This is awesome.”
We will perform default and by vbTextCompare in the Split function and see the difference in results:
Default:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub split_ex() Dim str Dim arr str = "This is a car. this is a toy. This is good. this is bad. This is awesome." 'Using split with dash and limit 2 arr = Split(str, "this") 'iterating through returned array For Each Item In arr sp_str = sp_str & vbNewLine & Item Next Item 'display the result MsgBox ("After Split: " & sp_str) End Sub |
Output:
By vbTextCompare:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub split_ex() Dim str Dim arr str = "This is a car. this is a toy. This is good. this is bad. This is awesome." 'Using split with dash and limit 2 arr = Split(str, "this", , vbTextCompare) 'iterating through returned array For Each Item In arr sp_str = sp_str & vbNewLine & Item Next Item 'display the result MsgBox ("After Split: " & sp_str) End Sub |
Result:
As we used “this” as the delimiter, the default compare value that performs case-sensitive split – it broke only where “this” “occurred.
On the other hand, as used vbTextCompare, it broke string from all instances of “this” or “This”.
