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