Forum Discussion

etienne m's avatar
etienne m
Copper Contributor
Apr 06, 2017

convert from single column to multiple columns

I have an list of addresses in one column Name1 Adress1 Postcode1 City1 Name2 Adress2 Postcode2 City2 Name3 Adress3 Postcode3 City3 Name4 Adress4 Postcode4 City4   Which must be...
Formulas & Functions
Show and Tell
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 06, 2017

Hi,

 

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

 

 

 

JoeMcDaid's avatar
JoeMcDaid
Icon for Microsoft rankMicrosoft
Apr 06, 2017

Assuming your data starts in A1. Place the following formula into B1:

 

 

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

 

And copy down and across.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 07, 2017

    Hi Joe,

     

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

    • etienne m's avatar
      etienne m
      Copper Contributor
      Apr 09, 2017

      Thanks for the reply. 

       

      At first instance it din't work. It only showed the formula in each cell.  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.

       

      Thanks for the support.

      Etienne

       

       

Resources