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.

[Greg Wilson]

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

"SAR" wrote:

>
> 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.
>
>
> --
> SAR
> ------------------------------------------------------------------------
> SAR's Profile: http://www.excelforum.com/member.php...o&userid=34172
> View this thread: http://www.excelforum.com/showthread...hreadid=564815
>
>

[Greg Wilson]

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

"Greg Wilson" wrote:

> 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
>
> "SAR" wrote:
>
> >
> > 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.
> >
> >
> > --
> > SAR
> > ------------------------------------------------------------------------
> > SAR's Profile: http://www.excelforum.com/member.php...o&userid=34172
> > View this thread: http://www.excelforum.com/showthread...hreadid=564815
> >
> >