Excel VBA – Split String into Cells

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on December 5, 2022

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! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples