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

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