convert from single column to multiple columns

Question

I have an list of addresses in one columnName1Adress1Postcode1City1 Name2Adress2Postcode2City2 Name3Adress3Postcode3City3 Name4Adress4Postcode4City4 &nbsp;Which must be converted into 4 columns:Name1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; adress1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; postcode1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; city1Name2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; adress2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; postcode2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; city2Name3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; adress3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; postcode3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; city4Name4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; adress4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; postcode4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; city4&nbsp;Any suggestions?I use Excell 2010 and 2016Regards,Etienne

sergeibaklan · Answer

Hi,&nbsp;As variant:- if your initial list in column A:A,- add index column (let say C) where starting from C1 it will be numbers 1,2,3,..- in D1 add formula=OFFSET($A$1,($C1-1)*4+0,0)in E1=OFFSET($A$1,($C1-1)*4+1,0)and simular to F1 and G1 (using ..*4+2 and ...*4+3)- copy cells from D1 to G1 down&nbsp;&nbsp;&nbsp;

joemcdaid · Answer

Assuming your data&nbsp;starts in A1. Place the following formula into B1:
&nbsp;
&nbsp;
=OFFSET($A$1,(ROW(A1)-1)*4+COLUMN(B1)-2,0)
&nbsp;
And&nbsp;copy down and&nbsp;across.

sergeibaklan · Answer

Hi Joe,&nbsp;Yes, but shall be updated a bit for the case if you insert row(s) and/or column(s) into the sheet

etienne m · Answer

Thanks for the reply.&nbsp;&nbsp;At first instance it din't work. It only showed the formula in each cell.&nbsp; After some google search I had to first type the = and then copy the rest of the formula in the first cell. Once this worked, extending to other cells was no problem.&nbsp;Thanks for the support.Etienne&nbsp;&nbsp;

Forum Discussion

convert from single column to multiple columns

Share

Resources