split column into 4 columns

J

Jack

I need some assitance making a huge column into 4 columns. Here is the
scenerio:
Col A has any given rows of values / text. and I need to split into 4
columns But this is where it gets tricky.
a1 =a
a2=b
a3=c
a4=d
a5=e
a6=f
a7=g
etc...
I need b1=a, c1=b, d1=c, e1=d
b2=e, c2=f, d2=g, etc..
What is the best way to do this?
 
D

Dave Peterson

Put this in B1:
=OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)
drag across to fill B1:e1

and drag down until you run out of data.
 
J

Jack

Dave,
One Word ---> PERFECT!
Thank you!
One additional question? How would I incorporate an if statement so that the
'0's dont show up if there isnt data?
 
P

Peo Sjoblom

How about using a custom format like

General;-General;

otherwise

=IF(OFFSET($A$1,4*(ROW()-1)+COLUMN()-2,0)="","",OFFSET($A$1,4*(ROW()-1)+COLU
MN()-2,0))
 
Top