Table of Contents
Splitting a string into an array can be done by using the split() method, which takes a separator character as a parameter and uses it to separate the string into an array of substrings. For example, the split() method can be used to separate a string of words into an array of individual words by using the space character as a separator.
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:
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.
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:
The Split function split each email in column A based on the @ symbol and outputted the resulting elements in columns B and C.
Note: You can find the complete documentation for the VBA Split function .
The following tutorials explain how to perform other common tasks using VBA: