How to Split a String into an Array in Excel VBA (4 Examples)

Working with text in Excel VBA often means breaking it into parts, for example, separating names, splitting values, or parsing CSV data. That’s where string splitting comes in. VBA gives you several ways to split a string into an array, depending on how complex your needs are.

In this article, we’ll explore different methods to split strings into arrays in Excel VBA, from the built-in Split function to advanced options like regular expressions. You’ll learn when to use each method and how to write clear, reliable code for your string parsing tasks.

Sub Split_MultipleDelimiters()
    Dim txt As String, cleanTxt As String
    Dim arr As Variant, i As Long
    txt = Range("A3").Value    ' Example: "Red;Green-Blue,Yellow"
    ' Normalize delimiters to comma
    cleanTxt = Replace(txt, ";", ",")
    cleanTxt = Replace(cleanTxt, "-", ",")
    arr = Split(cleanTxt, ",")  ' Now safe to split
    ' Show result in Immediate window
    For i = LBound(arr) To UBound(arr)
        Debug.Print "Item " & i & ": " & arr(i)
    Next i
    ' Write results across B3:E3
    Range("B3").Resize(1, UBound(arr) + 1).Value = arr
End Sub

Sometimes your data arrives as one long, comma‑separated list in a single cell, and you need each item individually for further processing. Instead of parsing the text character‑by‑character, you can pass it to VBA’s built‑in Split function, which turns the string into a zero‑based array in a single line.

For this demonstration, we’ll use a worksheet that has cell A2 containing Apple,Banana,Cherry.

The macro will split that text into an array, print each element with its index, and then write the three fruits across cells B2:D2 so you can see the result immediately. This confirms that Split handled the text correctly and that the array is ready for looping or direct output.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, then choose Insert >> Module.

➤ Paste the following code:

Sub Split_BasicExample()
    Dim txt As String, parts As Variant, i As Long
    txt = Range("A2").Value          ' Apple,Banana,Cherry
    parts = Split(txt, ",")          ' Split at each comma
    ' Show each element and its index in the Immediate window
    For i = LBound(parts) To UBound(parts)
        Debug.Print "Index " & i & ": " & parts(i)
    Next i
    ' Write the array horizontally starting in B2
    Range("B2").Resize(1, UBound(parts) + 1).Value = parts
End Sub

➤ Return to Excel and press  F5  to run the macro.

➤ Check the Immediate window (Ctrl + G) to see the indexed list, and confirm that B2:D2 now show Apple, Banana, and Cherry.

Sometimes your text isn’t clean, one cell might use commas, semicolons, and even dashes as separators. VBA’s Split only accepts one delimiter, but there’s a clever workaround. You can use Replace to convert every unwanted separator into one common one (like a comma), then apply Split.

We’ll convert all three delimiters (;, –, and ,) into commas, then split them into an array of four color names. The result is shown in the Immediate window and also written into cells B3:E3.

Steps:

➤ Press  Alt  +  F11  , insert a new module.
➤ Paste this code:

Sub Split_MultipleDelimiters()
    Dim txt As String, cleanTxt As String
    Dim arr As Variant, i As Long
    txt = Range("A3").Value    ' Red;Green-Blue,Yellow
    ' Normalize delimiters to comma
    cleanTxt = Replace(txt, ";", ",")
    cleanTxt = Replace(cleanTxt, "-", ",")
    arr = Split(cleanTxt, ",")  ' Now safe to split
    ' Show result in Immediate window
    For i = LBound(arr) To UBound(arr)
        Debug.Print "Item " & i & ": " & arr(i)
    Next i
    ' Write across B3:E3
    Range("B3").Resize(1, UBound(arr) + 1).Value = arr
End Sub

➤ Run the macro with  F5  , then view B3:E3 and the Immediate window.

Built‑in functions work for most cases, but sometimes you need surgical control, perhaps you must skip empty tokens, treat consecutive delimiters specially, or handle very large strings more efficiently. By looping through the text with InStr to locate each delimiter and Mid$ to extract the pieces, you can build the array yourself and decide exactly what happens at every step.

For this demo, cell A4 contains alpha beta    gamma (words separated by one or many spaces). The macro walks the string, ignores blank segments created by extra spaces, and returns a tidy array of three words. It then prints the result in the Immediate window and writes the words into B4:D4.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, then choose Insert >> Module.
➤ Paste the code below:

Sub Split_WithLoop()
    Dim txt As String, delim As String
    Dim pos As Long, nextPos As Long, token As String
    Dim pieces() As String, count As Long
    txt = Range("A4").Value          ' alpha  beta    gamma
    delim = " "                      ' single space delimiter
    pos = 1
    Do
        ' Find next delimiter
        nextPos = InStr(pos, txt, delim, vbBinaryCompare)
        If nextPos = 0 Then
            token = Mid$(txt, pos)   ' last piece
        Else
            token = Mid$(txt, pos, nextPos - pos)
        End If
        ' Skip empty tokens caused by multiple spaces
        If Len(token) > 0 Then
            ReDim Preserve pieces(count)
            pieces(count) = token
            count = count + 1
        End If
        If nextPos = 0 Then Exit Do
        pos = nextPos + 1            ' move past delimiter
    Loop
    ' Display in Immediate window
    Dim i As Long
    For i = LBound(pieces) To UBound(pieces)
        Debug.Print "Word " & i & ": " & pieces(i)
    Next i
    ' Write to B4:D4
    Range("B4").Resize(1, UBound(pieces) + 1).Value = pieces
End Sub

➤ Return to Excel and press  F5  to run the macro.
➤ Check  Ctrl  +  G  (Immediate window) for indexed output, and confirm B4:D4 displays alpha, beta, gamma.

Sometimes your string contains multiple different delimiters or complex patterns that the native Split function cannot handle. Using the VBScript Regular Expression object’s .Split method lets you define a pattern for all delimiters and split the string accordingly. This method is perfect for splitting on multiple delimiters at once or using advanced rules like look-ahead or look-behind.

For this demonstration, we’ll split a string containing commas, semicolons, and spaces as delimiters.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor, then Insert >> Module.
➤ Paste the following code:

Function RegExSplit(ByVal text As String, ByVal pattern As String) As Variant
    Dim re As Object
    Dim matches As Object
    Dim result() As String
    Dim i As Long, lastPos As Long
    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.Pattern = pattern
    Set matches = re.Execute(text)
    ReDim result(0 To matches.Count)   ' One more than matches count
    lastPos = 1
    For i = 0 To matches.Count - 1
        result(i) = Mid(text, lastPos, matches(i).FirstIndex - lastPos + 1)
        lastPos = matches(i).FirstIndex + matches(i).Length + 1
    Next i
    ' Last substring after last delimiter
    result(matches.Count) = Mid(text, lastPos, Len(text) - lastPos + 1)
    RegExSplit = result
End Function
Sub RegexSplit_MultiRows()
    Dim ws As Worksheet
    Dim text As String
    Dim lines As Variant
    Dim arr As Variant
    Dim i As Long, j As Long
    Dim outputRow As Long
    text = "Apple,Banana,Cherry" & vbNewLine & _
           "John-Mary;Steve" & vbNewLine & _
           "alpha  beta       gamma" & vbNewLine & _
           "Tom" & vbNewLine & _
           "Cat Dog Mouse" & vbNewLine & _
           "Jan:Feb:Mar:Apr" & vbNewLine & _
           "1001#1002#1003#1004"
    lines = Split(text, vbNewLine)
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change sheet name as needed
    ws.Cells.Clear
    outputRow = 1
    For i = LBound(lines) To UBound(lines)
        arr = RegExSplit(lines(i), "[,; \-:#]+")
        For j = LBound(arr) To UBound(arr)
            If Trim(arr(j)) <> "" Then
                ws.Cells(outputRow, j + 1).Value = Trim(arr(j))
            End If
        Next j
        outputRow = outputRow + 1
    Next i
    MsgBox "Splitting complete!"
End Sub

➤ Run the macro (F5). The Immediate Window (Ctrl+G) shows each fruit on its own line, split by commas, semicolons, or spaces.

Frequently Asked Questions

What happens if my string has two delimiters in a row when I use Split()?

Split returns an empty element for every “missing” value between consecutive delimiters. Loop through the array and skip Len(part)=0 items, or use a custom loop/RegExp to ignore blanks.

Can I split on several different delimiters without writing complex code?

Yes, replace every delimiter with one common character and then call Split. For example:

clean = Replace(Replace(txt, “;”, “,”), “-“, “,”)
arr   = Split(clean, “,”) 

This handles any number of delimiter types in one pass.

When should I choose a RegExp split over the native Split function?

Use RegExp when you need pattern‑based rules: multiple delimiters in one pattern, look‑ahead/look‑behind, or trimming repeated spaces/tabs. RegExp also lets you ignore delimiter cases or optional symbols.

How do I quickly write a split array back to the worksheet?

Resize a range to the array’s bounds and assign the Variant directly:

Range(“B2”).Resize(1, UBound(arr) + 1).Value = arr 

This writes all elements in one operation, far faster than looping cell‑by‑cell.

Wrapping Up

Splitting strings into arrays is a common task in Excel VBA, whether you’re working with CSV data, parsing user input, or breaking apart values for further processing. In this article, we explored four practical methods, from the built-in Split function to powerful RegExp-based splitting, so you can choose the one that best fits your needs. Use simple Split for most tasks, and switch to custom loops when you need more flexibility or control.