Excel: Convert single column of repeating text into multiple rows

Question

Easy excel question? (I've looked, and looked...)

How do you take a single column with groupings of TEXT (e.g. A1:A5, A6:A10, A11:A15) and convert those groups into rows (e.g. A1:A5 converts to B1:F1; A6:A10 converts to B2:F2) Thanks. (Note: can't upload image b/c I'm new)

Here's a text example:

CURRENT: (Convert this column (2000 entries in groups of 5))

Trinity
Neo
Morpheous
Agent Smith
Oracle
Dozer
Mouse
Zion
Nebuchadnezzar
The Twins

REFORMATTED: (Data in rows from the groups of 5)

Trinity | Neo | Morpheous | Agent Smith | Oracle
Dozer | Mouse | Zion | Nebuchadnezzar | The Twins

Gary's Student · Accepted Answer · 2015-03-14 13:31:37Z

1

With data in column A, in C1 enter:

=INDEX($A$1:$A$2000,COLUMNS($A:A)+5*(ROWS($1:1)-1),1)

Then copy C1 to D1 through G1

Then copy C1 through G1 downwards. For example:

enter image description here

answered Mar 14, 2015 at 13:31

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven Martin · Accepted Answer · 2015-03-14 13:44:37Z

1

Obviously Gary's way is much quicker , but an easy to understand but dirty way is to do like so

Add Formula's like so( if you are not putting a header row in , then use =MOD(ROW(A2),5)

enter image description here

The data will look like so

enter image description here

Then filter to 1 on the last column

enter image description here

edited Mar 14, 2015 at 13:44

answered Mar 14, 2015 at 13:35

Steven Martin

3,3201 gold badge23 silver badges29 bronze badges

Collectives™ on Stack Overflow

Excel: Convert single column of repeating text into multiple rows

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related