How to Split a String into an Array in VBA (3 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. You’ll learn to split a string in VBA in all types of ways possible.


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

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.

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

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.

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

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

VBA Code to Split a String into an Array in VBA

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.

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.

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.


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

ExcelDemy
Logo