Splitting a cell into rows instead of columns

[Tix]

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?

 

[DILipandey]

Hi Tix,

Use following logic / formula, supposing you have the data in cell A1:-
Enter following formula in B1:-

=MID(A1,1,1)

Enter following formula in B2 and drag the formula till end 600 rows or more
(per situation):-

=MID($A$1,FIND(B1,$A$1)+2,1)
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Mike H]

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]

Hi Dil, i tried that, i got A in B1 and then when i dragged the formula that
i copied into B2 and got an a in B2, i then dragged it down and got a blank
in B3, i in B4, g in B5, r in B6, this then repeats itself over and over.

 

[Jarek Kujawa]

why not copy->PasteSpecial->Values, select Transpose to some other
location
apply text to columns
then come back with data in needed format
?

 

[Tix]

Hi Mike,

I cannot do text to columns as the amount of data is greater than the amount
of columns in excel. I need to do 'text to rows' essentially but do not know
how.

 

[Tix]

Because the amount of data in the cell is bigger than the number of columns
available. If i do text to columns then i lose about half my data as the
columns run out.

I cannot transpose it as it is, as it is all in one cell.

 

[Jarek Kujawa]

oopps, sorry misread yr post

presume your semicolon delimited data is in A1

then insert the following formulae:

B1: =LEFT(A1,FIND(";",A1)-1)
A2: =MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1))
B2: =LEFT(A2,FIND(";",A2)-1)

then drag/copy down as needed

HIH

 

[Tix]

I do not understand what any of that is but it worked so thanks very much

 

[Don Guillett]

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

 

[Tix]

That might as well have been written in chinese, not being an expert in Excel
that was incomprehensible lol

Jarek gave me some formulas above however which seem to work. Just wish
Excel had a text to rows option as it does with text to columns.

 

[Jarek Kujawa]

thanks for your feedback

it is a custom in this NG to say thanks also to those whose solutions
were not used
Don gave you a neat macro...

 

[Jarek Kujawa]

pls note however that my formulae will give you correct result until
the last but one fragment/element
I should work a bit more to adjust them to cover la\\also the last one
element