Excel 2007 : 1000 rows into 10 columns in Excel 2007

[excelforum123]

Hi.

I have a csv file with 50000 rows of text in one column (that is 50000 cells).

I want to turn this into a 10 column wide and 5000 row deep area.

How do I do that ?

Thanks in advance.

(yes, the title says 1000 rows and 10 columns, my bad)

[TMS]

With your data in column A, if you put the following formula into cell B1 and copy down and across, it will split the data in columns of 10 values (formulae)

Please Login or Register  to view this content.

If you change the 10 to 20, you get columns of 20 values

Please Login or Register  to view this content.

Note that if you drag down more than you need, the formulae will just give extra entries.

So, just change the 10 (or 20) to however many values you want in a column.

Once you've split the data, copy it and paste special values and delete column A

Regards

[excelforum123]

Thx TmShucks. Testing now. Just one question, what do you mean by "copy down and across" ?

[TMS]

Put the formula in cell B1. Then fill down to cells B2, B3, ... B5000. Then fill (drag) across for the 10 columns you want.

Basically, you want the same formula in cells B1 through to K5000.

Regards

[excelforum123]

I get it now. Wow. Spent so much time on this today. You really saved my sanity there TMShucks.

Thanks a lot !

[excelforum123]

Now how do I mark this as solved ?

[TMS]

Look at the FAQs ;-)

"To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save"
Regards