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

Get FREE Advanced Excel Exercises with Solutions!

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


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:

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

Things to Remember
  • 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: 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:

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

Things to Remember
  • 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: 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:

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

Things to Remember
  • 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.

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo