excel - Move content from 1 column to 3 columns

Question

I have long file with this content:

John Doe1
Street Foo 35
New York
Mary Johnson
Street Foo 49
Los Angeles
Robert Deniro
Street Bar 47
Washington
...

As you can see there is one column with Name, Address, Town, Name, Address, Town..

I would like to convert it automatically to a file with 3 colums:

John Doe1        Street Foo 35     New York
Mary Johnson     Street Foo 49     Los Angeles
Robert Deniro    Street Bar 47     Washington
...

Is that possible?

Excel 2007/Windows 7

Answer 1

Based on this similar answer, and assuming your data lays in A1:A50 (you can extend this range in the formulas below as needed), then:

Cell B1: =INDEX($A$1:$A$50,ROW()*3-2,1)

Cell C1: =INDEX($A$1:$A$50,ROW()*3-1,1)

Cell D1: =INDEX($A$1:$A$50,ROW()*3,1)

This gives you one line of rearranged data. Copy B1:D1 downwards, as far as needed.