Excel VBA – Split String into Cells

This tutorial will demonstrate how to split strings of text into cells in VBA.

Split String into Cells

In VBA, we can use the Split Function to split a string of text into an Array.

Note: We wrote an entire mega-guide to using the Split Function in VBA. We highly recommend that you check it out.

Then we can loop through the array, outputting the split text into Excel cells:

Sub SplitBySemicolonExample()
    'Define variables
    Dim MyArray() As String, MyString As String, I As Variant, N As Integer
    
    'Sample string with semi colon delimiters
    MyString = "john@myco.com;jane@myco.com;bill@myco.com;james@myco.com"
    
    'Use Split function to divide up the component parts of the string
    MyArray = Split(MyString, ";")
    
    'Clear the worksheet
    ActiveSheet.UsedRange.Clear
    
    'iterate through the array
    For N = 0 To UBound(MyArray)
        'Place each email address into the first column of the worksheet
        Range("A" & N + 1).Value = MyArray(N)
    Next N
End Sub

Alternatively, we can use the Transpose Function to output the array into a worksheet:

Sub CopyToRange()
    'Create variables
    Dim MyArray() As String, MyString As String
    
    'Sample string with space delimiters
    MyString = "One,Two,Three,Four,Five,Six"
    
    'Use Split function to divide up the component parts of the string
    MyArray = Split(MyString, ",")
    
    'Copy the array into the worksheet
    Range("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)
End Sub

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!