VBA Split String into Array

Excel VBA Split String into Array

In VBA, we already know and use different types of data types. In this article, we are going to see VBA Split String into Array. This means we will using the SPLIT function in VBA to split the String or text line into an Array or put the string into different lines by splitting the words and characters used in it. For this, we will be using STRING as a data type because it will be helpful for us to store the data as text.

How to Use VBA Split String into Array?

To execute VBA Split String Into Array, we will be using SPLIT Function. Split function, breaks a string or text or sentence into a one-dimensional array. This means the broken string will be in the form of a list.

Below is the syntax of the VBA Split function:

• Expression: String which we want to split.
• Delimiter: Separator by which we want to split.
• Limit As Long: It is used to count the number of parts we want as a result.
• Compare: This is used to specify the type of comparison which can be done using Text, Binary, or Database.

For example, if we enter a string or text “THIS IS MY HOME.” Then each word into this string will be placed into a different line, just like splitting the text into an array.

Example #1

In this example, we will see how to just use a string or text and split it using the SPLIT function.

Step 1:  For this, insert a module from the Insert menu tab as shown below.

Step 2: In the newly opened module, write the subprocedure of VBA Split String into an array in any name or better in performed operations as shown below.

Code:

Sub StringArray2()

End Sub

Step 3: Define a variable using DIM as String where we will be storing the text which we want to split.

Code:

Sub StringArray2()

Dim TextString As String

End Sub

Step 4: Now define another variable using String again which will be used to store the final split value.

Code:

Sub StringArray2()

Dim TextString As String
Dim Result() As String

End Sub

Step 5: Choose a text which we want to split and assign that text to the first variable TextString as shown below. We have chosen a simple text as “This is a trick!”.

Code:

Sub StringArray2()

Dim TextString As String
Dim Result() As String
TextString = "This is a trick!"

End Sub

Step 6: Now use the SPLIT function with the variable TextString which has our text value. And assign it to the variable Result() variable as shown below.

Code:

Sub StringArray2()

Dim TextString As String
Dim Result() As String
TextString = "This is a trick!"
Result = Split(TextString)

End Sub

Step 7: To see the output, we will be using the Message box with the variable of string.

Code:

Sub StringArray2()

Dim TextString As String
Dim Result() As String
TextString = "This is a trick!"
Result = Split(TextString)
MsgBox TextString

End Sub

Step 8: Now if we compile the code and run it, we will see the output message box with the complete text which we have stored in variable TextString as shown below.

Step 9: As we can see in the above message box, the entered text is still in the same line. To properly execute the SPLIT function in VBA we need to follow the proper syntax which we have seen at the start. Use the delimiter by which we want to split the text. Here we are choosing a SPACE (“ “) as shown below.

Code:

Sub StringArray2()

Dim TextString As String
Dim Result() As String
TextString = "This is a trick!"
Result = Split(TextString, " ")

End Sub

Step 10: Now using the message box we will again see the text that we want to see in different array line. Considering the first word at the 0^th position, 2^nd word at the 1^st position, we will mark all the words from the lines as shown below.

Code:

Sub StringArray2()

Dim TextString As String
Dim Result() As String
TextString = "This is a trick!"
Result = Split(TextString, " ")
MsgBox Result(0) & vbNewLine & Result(1) & vbNewLine & Result(2) & vbNewLine & Result(3)

End Sub

Step 11: Now once done, compile the code using the F8 function and it there is no error found, run the code using the Play button. We will get the message box where each work from the text we used is split into Array as shown below.

Example #2

There is another simple example to execute Split String.

Step 1: For this again open a module and write the subprocedure for VBA Split as shown below in any name.

Code:

Sub StringArray3()

End Sub

Step 2: Use the same variable which we have seen in the above example. Here we have just change the data type of TextString as a Variant.

Code:

Sub StringArray3()

Dim TextString As Variant
Dim Result() As String

End Sub

Step 3: Now similar to example-1, assign a text to Result() variable using the SPLIT function as shown below. Here we chose names Ashwani, Rahul, Anjali and Sonia separated by semi-colons (“;”) and put semi-colon as separator required field.

Code:

Sub StringArray3()

Dim TextString As Variant
Dim Result() As String
Result() = Split("Ashwani;Rahul;Anjali;Sonia", ";")

End Sub

Step 4: Now open For Each loop where define the condition for each TextString in Result variable run the loop.

Code:

Sub StringArray3()

Dim TextString As Variant
Dim Result() As String
Result() = Split("Ashwani;Rahul;Anjali;Sonia", ";")
For Each TextString In Result
Next

End Sub

Step 5: And to see the output we will use Debug Print here whose result will be seen in Immediate Window.

Code:

Sub StringArray3()

Dim TextString As Variant
Dim Result() As String
Result() = Split("Ashwani;Rahul;Anjali;Sonia", ";")
For Each TextString In Result
Debug.Print TextString
Next

End Sub

Step 6: Now if we run the code, we will be able to see the use text got split into a different array in Immediate Window as shown below.

Pros of VBA Split String into Array

• The SPLIT function can be used where we need to split the text which has any kind of separator.
• Any kind of text can be separated using the Split function but the separator should be one in the single syntax.

Things to Remember

• If we are using the message box to see the separated values then we need to number each word there in the text. And start numbering with 0 for the first word and then 1, 2 and so on.
• For lengthy text, we can either use the text sequence in one go, or we can split the text sequence and for the next line, we can use underscore which would help us to put code in the different lines.
• We can use array and loop, both in the same code to make it dynamic.
• Once we are done with writing code, we can save the excel file in Macro enabled format, which is used to retain the VBA code of Excel.

Recommended Articles

This is a guide to the VBA Split String into Array. Here we discuss how to Use VBA Split String into Array in VBA Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. How to Use VBA Login?
2. VBA ByVal | How to Use ByVal Statement?
3. VBA Set Range | How to Use Set Range in VBA Excel?
4. VBA Format Date | Examples with Excel Template