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

as long as it follows that format of always three entries then a simple for loop should do the trick. — Sorceri, Dec 18 '13 at 19:22
Ziiweb, you might consider marking the accepted answer then. — sancho.s ReinstateMonicaCellio, May 05 '19 at 11:50

score 1 · Answer 1 · edited May 23 '17 at 11:50

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.

enter image description here

edited May 23 '17 at 11:50

Community

1
1

answered Dec 19 '13 at 06:06

sancho.s ReinstateMonicaCellio

14,708
20
93
185

score 0 · Answer 2 · answered Dec 18 '13 at 19:23

I am sure answered many times (and in different ways) on SO already but since I can't presently find an example you might try:

Add a column with =MOD(ROW(),3) copied down to suit and convert to values. Copy your data column twice, deleting top cell from first copy and top two cells from second. Filter to delete rows other than with 1 in what was the MOD column.

Move content from 1 column to 3 columns

2 Answers2

Linked