VBA: How to Split String Based on Multiple Delimiters

You can use the following basic syntax to split a string based on multiple delimiters in VBA:

Sub SplitString()

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

    For i = 2 To 7
    newString = Replace(Range("A" & i), "-", " ")
    SingleValue = Split(newString , " ")
    
       For j = 1 To 3
           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 either dashes or spaces in each string and then assigns the resulting elements of each string into the adjacent cells in columns B, C and D.

Note: This macro first replaces each dash with a space, then splits each string based on where the spaces occur.

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

Example: Split String Based on Multiple Delimiters in VBA

Suppose we have the following list of names in Excel:

Suppose we would like to split each name based on either dashes or 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 SingleValue() As String
    Dim i As Integer
    Dim j As Integer

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

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

VBA split string based on multiple delimiters

Notice that this macro splits each string in column A based on spaces and dashes and outputs the individual text elements of each string into columns B, C and D.

Note #1: We split strings based on dashes and spaces in this example, but you can split strings based on any delimiters you’d like by specifying them in the Replace and Split functions.

Note #2: You can find the complete documentation for the VBA Split function here.