VBA Split String into Array

What is Excel VBA Split String into Array?

The VBA Split String into Array function uses a designated delimiter to divide a string into smaller substrings, which can then be stored in an array. The original string’s elements can then be accessed and modified. Because of its versatility and ability to work with different delimiters, the VBA Split String into Array function is an effective tool for parsing and analyzing text data.

Let us look at an example where we have a string, “This is a sample sentence,” and we want to split it into an array of individual words.

We use the space character (” “) as the delimiter in the VBA Split String into Array function. This code snippet breaks the input string into separate words and then iterates through the resulting array to display each word using the “Debug.Print” statement. The output will be a list of words: “This,” “is,” “a,” “sample,” and “sentence.”

How to Convert Split String into an Array in Excel VBA?

To split a string into an array using the VBA Split String into Array function, follow these steps:

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: Inside the new module, declare the necessary variables. You’ll need a variable to hold the original string and another to store the resulting array.

• For example: Dim myString As String

Step 4: Assign the string you want to split to the myString variable.

• For example: myString = “This is a sample string”

Step 5: Next, divide the string into an array using the VBA Split String into Array method. Give the delimiter as the second argument and the string variable (myString) as the first.

• For example, to split by spaces: myArray = Split(myString, ” “)

Step 6: You can now access and manipulate the individual elements of the array. For instance, you can loop through the array or access specific elements using indexes.

Step 7: Add any additional code or logic you need to work with the array as per your specific requirements.

Step 8: Save your code and close the VBA Editor. To run your macro, press Alt + F8, select your macro, and click “Run.”

Examples

Example #1

If you have a string of names that are separated by commas and you would like to split it into an array, you can use the VBA Split String into Array function as follows:

Step 1: In this step, we create a VBA macro named SplitCommaSeparatedString and declare two variables: myString to hold the input string, and myArray to store the resulting array.

Step 2: We assign the string “John, Doe, Alice, Bob” to the myString variable. It is the string we want to split.

Step 3: Here, we apply the Split function to the myString variable. We specify “,” as the delimiter, indicating that we want to split the string wherever there is a comma.

Step 4: In this step, we use a loop to iterate through myArray and print each element using Debug.Print. We start from the lower bound (first element) and go to the upper bound (last element) of the array.

Step 5: Save the macro and run this code, it will display the following in the Immediate window.

Here is the complete code:

Sub SplitCommaSeparatedString()

    Dim myString As String

    Dim myArray As Variant

        myString = “John,Doe,Alice,Bob”

    myArray = Split(myString, “,”)

        ‘ Access and display array elements

    For i = LBound(myArray) To UBound(myArray)

        Debug.Print myArray(i)

    Next i

End Sub

Example #2

If you have a multiline string and want to split it into an array based on newline characters, you can use the VBA Split String into Array by newline function in the following way:

Step 1: We create a new macro named SplitByNewline and declare the same two variables as in Example #1.

Step 2: Here, we assign a multiline string to myString, using & vbCrLf & to represent a newline character between each line.

Step 3: We apply the VBA Split function to myString, using vbCrLf (a predefined constant for newline) as the delimiter to split the string into an array of lines.

Step 4: Similar to Example #1, we loop through the myArray and print each line.

Step 5: When you execute this code, it will display the following in the Immediate window:

Here is the complete code:

Sub SplitByNewline()

    Dim myString As String

    Dim myArray As Variant

        myString = “Line 1” & vbCrLf & “Line 2” & vbCrLf & “Line 3”

    myArray = Split(myString, vbCrLf)

        ‘ Access and display array elements

    For i = LBound(myArray) To UBound(myArray)

        Debug.Print myArray(i)

    Next i

End Sub

Example #3

In this example, we will split the date string “2024-01-04” into its constituent parts (year, month, day) and then distribute these components into separate cells within the first row of an Excel worksheet.

Step 1: In this step, we define a VBA macro named SplitDateString.

Step 2: Here, we declare a variable DateString as a String to hold our date string. We assign the date string “2024-01-04” to it using the date format in excel “YYYY-MM-DD.”

Step 3: In this step, we declare an array variable DateArray() to store the elements of the split date string. We use the Split function to break the DateString into an array of elements using the “-” delimiter. As a result, DateArray will contain three elements: “2024,” “01,” and “04.”

Step 4: Now, we declare an integer variable i to use as a loop counter. We then enter a For loop that iterates from the lower bound (LBound) to the upper bound (UBound) of the DateArray.

Step 5: In each iteration, we use Cells (1, i + 1) to specify the cell in the first row and the i+1-th column. The +1 is necessary because Excel column indexing starts from 1 and not 0.

Set the “.Value” property of the cell to the corresponding element of the DateArray using DateArray(i).

Step 6: When you run this macro, it will split the date string “2024-01-04” into its components and populate the cells in the first row of the Excel worksheet with “2024,” “01,” and “04” in separate columns, allowing you to work with the date components individually.

Here is the complete code:

Sub SplitDateString()

    Dim DateString As String

    DateString = “2024-01-04”

    Dim DateArray() As String

    DateArray = Split(DateString, “-“)

    Dim i As Integer

    For i = LBound(DateArray) To UBound(DateArray)

        Cells(1, i + 1).Value = DateArray(i)

    Next i

End Sub

Important Things To Note

1. Always declare variables for your original string and the resulting array before using them in your VBA code. It ensures that you have the appropriate data structures in place.
2. Clearly define the delimiter on which you want to split the string. The delimiter can be a single character, a string, or even a combination of characters to meet your specific splitting needs.
3. Indexes make it simple to access and modify specific components of the array once you have it. It gives you the ability to work with the divided parts of the original string using different data operations.
4. If you work with user-generated input in your VBA code, consider including error handling. It guarantees that your code can deal with unforeseen eventualities and circumstances where the delimiter may not be present in the string properly.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Split String into Array. Here we learn How to Convert Split String into an Array in Excel VBA along with examples. You can learn more from the following articles –

• VBA String Array
• VBA String to Integer
• VBA String Functions