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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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!
