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.

excel vba split string into array


VBA Split Function (Quick View)

=Split(Expression As String, [Delimiter], [Limit As Long=1], [CompareAsVbCompareMethod=vbBinaryCompare])

Syntax to Split String into Array in VBA


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

VBA Code to Split a String into an Array in VBA

Output:

It will split the string into an array consisting of {“We applied for the visa of U.S”, “ Canada”, “ Australia”, “ France”}.

VBA Output

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

VBA Code to Split a String into an Array in VBA

Output:

It will split the string into an array consisting of {“We”, “applied”, “for”, “the”, “visa”, “of”, “U.S,”, “Canada,”, “Australia,”, “France,”}.

Output to Split a String into an Array in VBA

Sample title
  • 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


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

Split String Output

Output:

It will split the string into an array consisting of the first 3 items separated by the delimiter space.

Split a String into an Array in VBA with Limited Items

Sample title
  • 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

VBA Code to Split a String into an Array in VBA

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.

VBA Output

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

Excel VBA Code

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.

VBA Output

Read More: Excel VBA: Remove Duplicates from an Array (2 Examples)

Sample title
  • 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.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo