Having trouble copying rows into columns

Microsoft Excel 2003 (full product)
August 3, 2010 at 09:36:42
Specs: Windows XP, 1G
Having trouble copying rows 1,2,3,4,5 into columns A,B,C,D,E. An example is this address:
1.Peter Cook
2.2382 SE 7th CT
3.Miami,Florida 33168
4.Adult High School
Need to spread this in the columns A,B,C,D,E

See More: Having trouble copying rows into columns

Report •

August 3, 2010 at 09:45:52

No need to shout ...

Select cells A1 to A4 (or to A5 if that's what you have)
Select cell B1
Right-click and choose 'Paste Special ...'
In the Paste special dialog box check the Transpose check box
Click OK

Select cell A1 and Right-click and choose Delete ...
In the Delete dialog box select 'Shift cells Left'
Click OK

Delete the remaining original data as required.


Report •

August 3, 2010 at 09:47:44
1 - You said rows 1,2,3,4,5 but you only included 4 pieces of data. Just a bit confusing, that's all.

2 - You didn't say what column that data was in, so I'll assume Column A.

3a - Select A1:A5
3b - Edit...Copy
3c - Select B1
3d - Edit...Paste Special...Transpose
3e - Delete Column A

4 - Respond with all the things that you didn't tell us in your original post.

Report •

August 3, 2010 at 10:21:31
Thanks,very much didn't mean to sound loud lol sorry, can I use this on multiple selections?

Report •

Related Solutions

August 3, 2010 at 10:51:16
Hi, my last question was can I use multiple selections with past special?

Report •

August 3, 2010 at 11:20:04

Give it a try and see what happens.

Just copy your data into a new workbook, and experiment.


Report •

August 3, 2010 at 11:30:53
re: "can I use this on multiple selections?"

By that I assume you mean that you have a large number of "address groups" and that you want to place them all into rows instead of a single column.

That is exactly why I said:

4 - Respond with all the things that you didn't tell us in your original post.

The answer is No, you can't use Paste Special...Transpose unless you want to do it manually, group by group.

Excel would have no way of knowing where one set of data (an address group) ends and the next one begins and is incapable of splitting the "paste" into multiple rows.

However, we have options: VBA is always a possiblity but this formula based solution might work for you, as long as you don't mind a few manual steps.

Based on the example you gave us, which is all we have to work with, I'll assume you have groups of 4 lines, A1:A4, then A5:A8, then A9:A12, etc.

If that's the case...

1 - Put this formula in B1 and drag it across to E1:


This should "transpose" your first address group.

2 - Select B1:E4 and press Ctrl-C to Copy it

3 - Select the range starting in B5 and ending in Column E on the last row of your last piece of data.

4 - Press Ctrl-V for Paste

5 - Copy the entire range and do a Copy...Paste Special...Values to get rid of the formulas.

6 - Delete Column A

7 - Sort the range to eliminate the blank rows and bring the transposed data to the top of the sheet.

Report •

Ask Question