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 0th position, 2nd word at the 1st 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 –