Rifat Hassan
In this article, I’ll show you how you can split a string into an array in VBA. Split is one of the most important and widely used functions that we use in VBA Macro. You’ll learn to split a string in VBA into an array in every possible way. Before jumping into the methods in the next section, let’s take a look at the GIF representing the overview of this article.
VBA Split Function (Quick View)
=Split(Expression As String, [Delimiter], [Limit As Long=1], [CompareAsVbCompareMethod=vbBinaryCompare])
Download Practice Workbook
3 Ways to Split a String into an Array in VBA
Let’s have a string in our hand “We applied for the visa of U.S, Canada, Australia, and France.”. I’ll show you how you can split this string into an array in all types of ways possible using the Split function of VBA.
1. Use Any Delimiter to Split a String into an Array in VBA
You can use any string as the delimiter to split a string into an array in VBA.
It can be a space (“ “), a comma (“,”), a semicolon (“:”), a single character, a string of characters, or anything.
⧭ Example 1:
Let’s split the string using the comma as the delimiter.
The line of code will be:
Arr = Split(Text, ",")The complete VBA Code will be:
⧭ VBA Code:
Sub Split_String()
Text = "We applied for the visa of U.S, Canada, Australia, and France."
Arr = Split(Text, ",")
Output = ""
For i = 0 To UBound(Arr)
Output = Output + vbNewLine + vbNewLine + Arr(i)
Next i
MsgBox Output
End Sub
⧭ Output:
It will split the string into an array consisting of {“We applied for the visa of U.S”, “ Canada”, “ Australia”, “ France”}.
⧭ Example 2:
You can also use a space (“ ”) as the delimiter.
The line of code will be:
Arr = Split(Text, " ")The complete VBA Code will be:
⧭ VBA Code:
Sub Split_String()
Text = "We applied for the visa of U.S, Canada, Australia, and France."
Arr = Split(Text, " ")
Output = ""
For i = 0 To UBound(Arr)
Output = Output + vbNewLine + vbNewLine + Arr(i)
Next i
MsgBox Output
End Sub
⧭ Output:
It will split the string into an array consisting of {“We”, “applied”, “for”, “the”, “visa”, “of”, “U.S,”, “Canada,”, “Australia,”, “France,”}.
- The default delimiter is a space (“ ”).
- That is, if you don’t insert any delimiter, it’ll use a space as the delimiter.
Read More: Split String by Character in Excel (6 suitable Ways)
Similar Readings
- VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
- Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
- How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
2. Split a String into an Array with Any Number of Items
You can split a string into an array with any number of items according to your wish.
Insert the number of items as the 3rd argument of the Split function.
⧭ Example:
Let’s split the string into the first 3 items with a space as the delimiter.
The line of code will be:
Arr = Split(Text, " ", 3)And the VBA code will be:
⧭ VBA Code:
Sub Split_String()
Text = "We applied for the visa of U.S, Canada, Australia, and France."
Arr = Split(Text, " ", 3)
Output = ""
For i = 0 To UBound(Arr)
Output = Output + vbNewLine + vbNewLine + Arr(i)
Next i
MsgBox Output
End Sub
⧭ Output:
It will split the string into an array consisting of the first 3 items separated by the delimiter space.
- The default argument is -1.
- That means, if you don’t input the argument, it will split the string into the maximum number of times possible.
Read More: How to Split String by Length in Excel (8 Ways)
3. Use Both Case-Sensitive and Insensitive Delimiter to Split a String an Array in VBA
The Split function offers you to use both a case-sensitive and case-insensitive delimiter.
For a case-insensitive delimiter, insert the 4th argument as 1.
And for a case-insensitive delimiter, insert the 4th argument as 0.
⧭ Example 1: Case-Insensitive Delimiter
In the given string, let’s consider the text “FOR” as the delimiter and 2 as the total number of items in the array.
Now, for a case-insensitive case, the line of code will be:
Arr = Split(Text, "FOR ", 3,1)And the complete VBA code will be:
⧭ VBA Code:
Sub Split_String()
Text = "We applied for the visa of U.S, Canada, Australia, and France."
Arr = Split(Text, "FOR", 2, 1)
Output = ""
For i = 0 To UBound(Arr)
Output = Output + vbNewLine + vbNewLine + Arr(i)
Next i
MsgBox Output
End Sub
⧭ Output:
As the delimiter is case-insensitive here, the “FOR” will work as “for” and it will split the string into an array of two items.
⧭ Example 2: Case-Sensitive Delimiter
Again, for a case-sensitive case, the line of code will be:
Arr = Split(Text, "FOR ", 3,0)And the complete VBA code will be:
⧭ VBA Code:
Sub Split_String()
Text = "We applied for the visa of U.S, Canada, Australia, and France."
Arr = Split(Text, "FOR", 2, 0)
Output = ""
For i = 0 To UBound(Arr)
Output = Output + vbNewLine + vbNewLine + Arr(i)
Next i
MsgBox Output
End Sub
⧭ Output:
As the delimiter is case-sensitive here, the “FOR” won’t as “for” and it won’t split the string into an array of two items.
Read More: Excel VBA: Remove Duplicates from an Array (2 Examples)
- The default value of the argument is 0.
- That is, if you don’t put the value of the 4th argument, it will work for a case-sensitive match.
Conclusion
Therefore, using these methods, you can use the Split function of VBA to split a string into an array of items. Do you have any questions? Feel free to ask us on our Exceldemy website.
