change row to column

[Humbled Learner]

Hi,

I imported an address book. Column A contains all the information, 430
lines consisting of 1 name, 2 address, 3 city, 4 State, 5 Zip, 6 name, 7
address, 8 city, 9 State, 10 Zip 11 name and so forth.

Is there an easy way to move the row to columns each in their own block?

 

[T. Valko]

Is there an easy way to move the row to columns

each in their own block?

Easy is a tricky term. It means different things to different people!

Let's assume your data is in the range A2:A21.

Enter this formula in C2:

=INDEX($A$2:$A$21,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Copy across to G2 then down to row 5.

After you have the data transposed inspect it and make sure it's correct.

Select the entire range of formulas
Goto the menu Edit>Copy
Then, Edit>Paste Special>Values>OK

Now you can delete the original data in column A (if you want to).