Split a string into array and paste into a sheet in Excel VBA

Grab the Free VBA Quick Reference Guide
The Split Function in VBA will split a string into a one-dimensional array. The text will be split based on the Delimiter. It is used to specify where the text should be split. In the code example below the text string is split with a space. Then we use the Ubound of the Array to resize the range to make it the same size and then make the range equal to the oarray.

Code

Sub SplitArray()
cells(1,1) = "How do I split an array"

oarray = Split(Cells(1, 1), " ")

Cells(2, 1).Resize(, UBound(oarray) + 1) = oarray

End Sub

[prrstories]

Thank yo so much its work for me.You are great man...keep posting videos..you are doing great work.

[annezach]

Thank you for this! What if there are multiple delimiters?

[karenblack]

Is it possible to transpose the output so that rather than it goes across a row it goes down a column? And finally if my last value doesn’t have the delimiter after it, how do I get this included?

[arielzabatecuizon]

Hi, can you convert this into a function? I want to use it like text to column but I can use any strings as delimiter. Please...