Separate One Cell Into Many Rows
 

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

Separate One Cell Into Many Rows
 

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

Separate One Cell Into Many Rows
 

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