Can you use Text To Columns feature to be Text To Rows?

[tinkerbell737]

I have a cell that has a comma separated value that is 354 fields long. As such, if I use the Text To Columns feature to split the data at each column, I lose several columns (because excel cannot have that many columns).

How can I break the data at the comma, but have it list in rows instead?

 

[shyy]

Excel 2010

	D
1	"FirstName","LastName","MiddleInitial","HomeAddress","HomeCity","HomeState"
2
3	FirstName
4	LastName
5	MiddleInitial
6	HomeAddress
7	HomeCity

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Turn Cell Into Rows and Columns​

Worksheet Formulas Cell Formula D3 =MID(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:1)-1)*LEN(D$1)+1,LEN(D$1))),2,LEN(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:1)-1)*LEN(D$1)+1,LEN(D$1))))-2) D4 =MID(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:2)-1)*LEN(D$1)+1,LEN(D$1))),2,LEN(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:2)-1)*LEN(D$1)+1,LEN(D$1))))-2) D5 =MID(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:3)-1)*LEN(D$1)+1,LEN(D$1))),2,LEN(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:3)-1)*LEN(D$1)+1,LEN(D$1))))-2) D6 =MID(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:4)-1)*LEN(D$1)+1,LEN(D$1))),2,LEN(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:4)-1)*LEN(D$1)+1,LEN(D$1))))-2) D7 =MID(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:5)-1)*LEN(D$1)+1,LEN(D$1))),2,LEN(TRIM(MID(SUBSTITUTE(D$1,",",REPT(" ",LEN(D$1))),(ROWS($1:5)-1)*LEN(D$1)+1,LEN(D$1))))-2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[compaq-excel-1001]

Thank you Mark W! You are top of the line A++++
Issue Resolved Pure GENIOUS Thank you!!!!!!!!!!

 

[Thangaraj S]

Say A1 = 1,2,3,4,5

=trim(mid(substitute(a$1,",",rept(" ",500)),row(a1)*500-499,500))

Excel 2010

	A	B
1	1,2,3,4,5	1
2		2
3		3
4		4
5		5

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B1 =TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",500)),ROW(A1)*500-499,500)) B2 =TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",500)),ROW(A2)*500-499,500)) B3 =TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",500)),ROW(A3)*500-499,500)) B4 =TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",500)),ROW(A4)*500-499,500)) B5 =TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",500)),ROW(A5)*500-499,500)) <tbody> </tbody>

<tbody>
</tbody>

Thankyou this can be work for single row. But I've the 420 datas in Row A1 to Row A420. How can use this for the multiple rows?

 

[shyy]

If this is a new question, you might want to post up a new thread with your sample data and how you want the result to look like.

 

[West Man]

Edit the document using Word (or your favorite system editor). Replace , with ^n.

Why does this work? What does the ^N do?

 

[shyy]

signifies a column break

Why does this work? What does the ^N do?

 

[smithbc]

Edit the document using Word (or your favorite system editor). Replace , with ^n.

Hi Mark W. Can you explain a bit more about the next steps once commas are replaced by ^n? I don't understand how that solves the issue, but perhaps I'm missing something obvious. Thank you.

 

[musicianpractition]

thanks

 

[musicianpractition]

Thankyou this can be work for single row. But I've the 420 datas in Row A1 to Row A420. How can use this for the multiple rows?

Yes.. I second this.... How do you apply this to multiple rows???

 

[musicianpractition]

Hi Mark W. Can you explain a bit more about the next steps once commas are replaced by ^n? I don't understand how that solves the issue, but perhaps I'm missing something obvious. Thank you.

Yeah.. I'm confused too.

Do you just mean replace all the commas in your data with ^N? I tried that, but when you go to do the text to columns it doesn't allow you to enter ^N. It only allows you to enter 1 character... Please clarify.