How to Split a String into an Array in VBA (3 Effective Ways)

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 a String into an Array in VBA: 3 Effective Ways

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 by a delimiter (i.e. comma).

The line of code will be:

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:

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,”}.

Read More: VBA to Split with Multiple Delimiters in Excel

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:

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.

Read More: Excel VBA: Split String into Rows

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-sensitive 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:

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:

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.

Download Practice Workbook

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

Related Readings

• Excel VBA: Split String into Cells
• Excel VBA: Split String by Character
• Excel VBA: Split String by Number of Characters
• VBA to Split String into Multiple Columns in Excel