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 = "firstname.lastname@example.org;email@example.com;firstname.lastname@example.org;email@example.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