3

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 at 13:44
5

In below example, formula in C1:

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

enter image description here

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

  • Better add IFERROR to avoid error if no value! +10 ☺ – Rajesh S Jul 18 at 7:57
3

You can use this formula:

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

enter image description here

  • ,, how to replace 0 with blanks,, formula returns when no value ? – Rajesh S Jul 18 at 8:19
  • Add IF function? – Lee Jul 19 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.