Hi. I'm wondering if there are any Excel experts out there who can help me to redistribute a whole load of cells without having to do so one by one (and thus also probably making mistakes)? I'm working with Excel 2000, and I've got a long itemised telephone bill. Each call consists of six pieces of information: date, time, number called, duration, cost... etc. Unfortunately, when I copied all that info from the web source, where each call appeared on a separate line, so that the categories were in handy columns, it pasted differently into Excel. Now, everything appears in column A. A1 is the date, A2 is the time, A3 is the number called, etc. This means that the information for the next call starts at A7 (date, time, etc.). This is obviously not very useful for sorting data, adding, etc. So does anyone know of an automated way of redistributing it all so that A1 goes to A1, A2 to B1, A3 to C1... and then A7 to A2, A8 to B2, A9 to C2, etc. etc.? I hope that's clear, but if not feel free to seek clarification... !

Copy your data from the web and paste it into NotePad or another plain text editor and save the file. (I use EditPad Lite (free for non-comercial user) because it does not have the 64kb limitation that NotePad has. If the data does not exceed 64kb then NotePad will work just fine.) Then open Excel and go to File, Open (change files of type to All Files), Browse to your saved .txt file and open it. You will get the wizard. Follow it's prompts. On Step one select Next, Step two Tab is already selected and you will see the separating bar/s on the contents below. Try selecting "Space" to see if that separates your data completely as desired. If it does then just hit Finish. Otherwise, if your data is separated by "-" (hyphens) or some other special character then additionally select "Other" and place the special character in the box and then click Finish. (I almost never go through Step #3 because I can format easily within Excel after importing the text.) There are a lot of words above but it is really quite simple once you know how to separate your specific data to how you desire it to be separated. (It's like; click, click, Finish) Regards, Bryan

Thanks for that. I've had a go at what you suggest, but it doesn't seem to make any difference. Trouble is, in the wizard all the data appears in the same way - in a single, unbroken column down the left. What I want is for the first item, and every sixth item thereafter, to appear in column one; the second item, and every sixth item thereafter, to appear in column two; and so on. None of the options in the wizard seem to provide that option (I've messed around with it for a while, and nothing seems to change the actual placement of the cells... ?!)

I'm not sure why your paste function would have pasted rows of data into single column. Never heard of that before. Are you sure that each row of data isn't really pasted into a single cell in Column A? That wouldn't suprise me and could easily be fixed via Data...Text to columns. If your data is really in separate cells then there are a few ways to fix your data: Copy...Paste Special...Transpose will allow you to Copy A1:A6 and Paste it into B1:G1 by choosing the Transpose option under Paste Special. You can then delete Column A After all your data has been moved. Another option would be run this code on your data, which basically repeats the Paste Special...Transpose feature over and over again until it runs out of data. Right click the sheet tab, choose View code, and paste the code into the VBA window. I suggest you save an extra copy of your file just in case somethng goes terribly wrong. Sub PST() LastRow = Cells(Rows.Count, "A").End(xlUp).Row For Rw = 1 To LastRow Step 6 Range(Cells(Rw, "A"), Cells(Rw + 5, "A")).Copy NxtRw = NxtRw + 1 Range("B" & NxtRw).PasteSpecial Transpose:=True Next Columns("A").Delete End Sub

Yes, aste Special, Transpose should do it. I have seen the data copied from a webpage appear in the single cell before. Did you paste the original data into NotePad or WordPad. I ask beacuse WordPad will save it as a .rtf format file versus plain text using NotePad. If all of the above is not working then I suspect the data you are using is an image and not text. Bryan