Excel VBA – Split String into Cells

This tutorial will demonstrate how to split strings into cells.

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!