Splitting a cell into rows instead of columns

T

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?
 
D

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
 
M

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
 
T

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.
 
J

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
?
 
T

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.
 
T

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.
 
J

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
 
D

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
 
T

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.
 
J

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...
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top