Columns to Rows to if more than 254.

[geniusideas]

In one cell i have many char seperated by comma example :

Cell A1 - C123,A244,A555,....And sometime more than 254 (total Column
in excel)
Cell A2 - C451,A123,.....
Cell A3 - B123,D245,...
Until >100 rows.

Now I am using text to column to seperated the char into individual
cell in column.
The problem now :

1) I have to arrange in rows not column..Pls help.
2) We only can arrange in column if qty < 254, if > How..
If we use macro what code to be use..

http://microsoft-excel-macro.blogspot.com

 

[Jim Thomlinson]

Something like this should work with your text string in A1 and the results
going int A2 -> A???

Public Sub TextToRows()
Dim arr As Variant
Dim rng As Range
Dim lng As Long

arr = Split(Range("A1"), ",")
Set rng = Range("A2")
For lng = LBound(arr) To UBound(arr)
rng.Value = arr(lng)
Set rng = rng.Offset(1, 0)
Next lng

End Sub

 

[Gary''s Student]

You are already aware that you can split up a cell horizontally by using Text
to Columns (up to 254 items). This samll macro:

Sub text_to_rows()
s = Split(Selection.Value, ",")
l = LBound(s)
u = UBound(s)
For i = l To u
Selection.Offset(i + 1, 0).Value = s(i)
Next
End Sub


will allow you to split it up vertically. Select any single cell and run
the macro. It will string the values down the column just below the Selected
cell.