Excel / VBA Split – How to Break a String
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:
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:
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:
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:
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:
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 a case-insensitive split.
To understand, consider this string:
We will perform default and by vbTextCompare in the Split function and see the difference in results:
Default:
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:
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 compares the 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”.
