Excel 2007 : Separating Data from Rows to a New Row

[teh_siggy0731]

Hello, Excel folks.

I have MS Excel 2007 and I want to know this: What's the quickest way for me to separate specific cells and move them into a new column without going through the long process of ctrl-clicking.

Image:

\1

If it can be done in a macro or something else, I would be definitely appreciate it. I want the addresses and the names to be on a new column. I have tried transposing it, but alas it made way too many columns.

Thanks.

[JBeaucaire]

If all your addresses are in 4 row sets...

1) All data is in column A

2) Enter this formula in B1:

=INDEX($A:$A, ((ROW(A1)-1)*4)+COLUMN(A1))

3) Copy that B1 cell across through C:E, then down as far as you need.

4) Highlight all the new data

5) Ctrl-C to copy

6) Edit > Paste Special > Values to remove the formulas

7) Delete column A.