VBA: How to Split String into Array (With Example)

You can use the following basic syntax to split a string into an array using VBA:

Sub SplitString()

    Dim SingleValue() As String
    Dim i As Integer
    Dim j As Integer

    For i = 2 To 7
    
    SingleValue = Split(Range("A" & i), " ")
    
       For j = 1 To 2
           Cells(i, j + 1).Value = SingleValue(j - 1)
       Next j
       
    Next i
    
End Sub

This particular example splits each string in the range A2:A7 based on spaces in each string and then assigns the resulting elements of each string into cell ranges B2:B7 and C2:C7.

The following example shows how to use this syntax in practice.

Example: Using VBA to Split String into Array

Suppose we have the following list of strings in Excel:

Suppose we would like to split each string based on spaces and then assign the resulting elements of each string to new cells.

We can create the following macro to do so:

Sub SplitString()

    Dim SplitValues() As String
    Dim i As Integer
    Dim j As Integer

    For i = 2 To 7
    
    SplitValues = Split(Range("A" & i), " ")
    
       For j = 1 To 2
           Cells(i, j + 1).Value = SplitValues(j - 1)
       Next j
       
    Next i
    
End Sub

When we run this macro, we receive the following output:

VBA split string into array

Notice that columns B and C contain the first and last names of each full name in column A.

Also note that you can use the VBA Split function to split an array based on a different delimiter.

For example, suppose we have the following list of emails:

Suppose we would like to split each email into an array based on where the @ symbol occurs.

We can create the following macro to do so:

Sub SplitString()

    Dim SplitValues() As String
    Dim i As Integer
    Dim j As Integer

    For i = 2 To 7
    
    SplitValues = Split(Range("A" & i), "@")
    
       For j = 1 To 2
           Cells(i, j + 1).Value = SplitValues(j - 1)
       Next j
       
    Next i
    
End Sub