Separate One Cell Into Many Rows

[SAR]

Cell A1 has 13,242 whole numbers listed, each separated by a semi-colon.
How can I separate all these numbers into 13,242 rows? I tried to do
columns to rows but Excel will not accomodate 13,242 columns. I put in
into Word, and still cannot figure it out. I need to do this process
every month with different numbers and a different amount of numbers in
Cell A1. Thanks for your help in advance.

 

[Guest]

Try:

Sub xyz()
Dim arr As Variant
arr = Split(Range("A1").Value, ";")
Range("A1").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)
End Sub

Regards,
Greg

 

[Guest]

I suspect that the Split function can't handle that many elements. I don't
have a burning desire to construct a test with a text string containing
35,242 numbers separated by semi-colons. If not, we should be able to come up
with something. Perhaps using a combination loop using Split to its max
capacity, pasting, and then reusing Split ??? Or just a simple loop and parse.

Greg