Partially transpose data which pasted as a single column

Question

When I paste data from certain applications, it ends up as:

A
B
C
D
E
F
G
H
I
J
K
L

But it's supposed to be:

A B C D
E F G H
I J K L

Is there an easy way to restructure?

You should show some real data which you are copying or else people will focus too much on succeeding with simply `A B C D E F G H I J K L`. It would be in your best interest to also provide a screenshot. — MonkeyZeus, Jul 18 '19 at 13:44

JvdV · Accepted Answer · 2019-07-18T13:55:57.090

5

In below example, formula in C1:

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

Drag down and right...

Basically the same as this answer on SO.

Note: You can change *4-4 to any number of columns you want to transpose into > *n-n

edited Jul 18 '19 at 13:55

answered Jul 18 '19 at 05:12

JvdV

Better add `IFERROR` to avoid error if no value! +10 ☺ – Rajesh Sinha Jul 18 '19 at 07:57

score 3 · Answer 2 · answered Jul 18 '19 at 07:16

3

You can use this formula:

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

answered Jul 18 '19 at 07:16

Lee

,, how to replace 0 with blanks,, formula returns when no value ? – Rajesh Sinha Jul 18 '19 at 08:19
Add **IF** function? – Lee Jul 19 '19 at 01:26

2 Answers2