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?

 

[TommyGun]

I don't know how to accomplish this without using VBA. I can give you some code if you would like. Otherwise, someone on this board might know a better way.

If you don't mind me asking, why is the field 350+ columns wide?

 

[tinkerbell737]

The file is actually only 1 column wide, but if I use the text-to-columns feature it will be 354 columns wide.

The CSV file that I have created is for a data bridge that I am using, and the information must be in specific fields in order for the data feed to work correctly. I have found that there is a problem some where inmy data file, and that everything is off by 2 places.

I need to be able to see the data in a row instead so that field 1 will be row 1, field 2=row 2, etc. This way I can see which field is missing information.

I have never used Visual Basic (to my knowledge) so I wouldn't know how to implement that to move the information.

My file is like this : "FirstName","LastName","MiddleInitial","HomeAddress","HomeCity","HomeState"... etc.

and there are 354 data fields. I need to be able to see that field 1 is FirstName, field 2 is LastName.

 

[Juan Pablo González]

How about importing the file to Access ?

 

[Legacy 12630]

You could first split your cell into two cells via TextToColumns (by Fixed Width & create your own break line), and then use TextToColumns on each of the two cells.

And then Copy>PasteSpecial>Transpose to put the data from columns to rows.
This message was edited by Ponsonby on 2003-01-13 17:32

 

[tinkerbell737]

I did try the import into access, but the file is still too large. I think Access can only have 255 columns.

 

[Mark W.]

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

 

[Malc London]

Mark W, you genius! I never thought of that.

You have just saved me so much time. Such a simple solution to an annoying problem I had.

 

[Constantly Learning]

Mark W, your suggestion is spot-on! I've been visiting Mr. Excel for years and came across this situation recently and your answer solved my dilemma in a matter of seconds. Thank you SO MUCH!!!

 

[shyy]

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
Sheet4

Cell Formulas
Range	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))