To rows assuming cell is J2

Sub stance()
SrcData = Range("j2").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
'Range("A" & SplitData + 1) = OutPutData(SplitData)
Cells(SplitData + 3, "j") = OutPutData(SplitData)
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H" wrote in message
...
Hi,

You can first do text to columns using ; as a delimeter then copy the
range
and then paste special - transpose. Or if you want a macro this takes the
data from a1 and splits it into column a starting in a2

Sub stance()
SrcData = Range("A1").Value
OutPutData = Split(SrcData, ";")
For SplitData = 0 To UBound(OutPutData)
Range("A" & SplitData + 1) = OutPutData(SplitData)
Next
End Sub

Mike

"Tix" wrote:

Hi, i have a cell with approx 600 names in it seperated by a semi colon.
I
want to get this data into rows, so one name per cell going down the page
in
list form. Text to columns does that for columns, but Excel doesnt have
enough columns for that much data (otherwise i could do text to columns
then
paste special and transpose to get it in a row).

Is there a similar command to text to columns that pastes the data from a
cell into rows rather than columns?