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?

 

[wonderbrett]

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.

Copy your row of data. Paste it into Word. Go to Find/Replace in Word, then replace the ";" or "," with "^n". This will add a line break after each entry. Then press Ctrl+A then Ctrl+C (copy all), then click in your first cell in Excel and press Ctrl+V (paste). Voila. Text to rows to columns.

 

[HungryMarco]

Copy your row of data. Paste it into Word. Go to Find/Replace in Word, then replace the ";" or "," with "^n". This will add a line break after each entry. Then press Ctrl+A then Ctrl+C (copy all), then click in your first cell in Excel and press Ctrl+V (paste). Voila. Text to rows to columns.

I found the easiest method is to replace ; with ^l

 

[Meryspondi]

I would make the text into columns first, then select and copy all the cells.
move to the cursor in the next cell down
At this point I would right click and select the "Transpose" option which should appear as the 4th icon in the "paste" section.
Hope this is of help.
Mx