How can I organize a vertical column of data in a spreadsheet?

David Fay April 10, 2012

I have a contact list with hundreds of names and full addresses etc. I have this data in both an Excel version and also Word version but all these complete addresses are in just one column with the name in the top row and the address in next row below and then there’s the next companies data as the column goes down the worksheet.

How can I get this vertical column of data into the regular horizontal set up where you have separate columns for name, address, zip code etc? Thanks!

  1. Oron Joffe
    April 11, 2012 at 7:33 pm

    If I understand you correctly, each item of your data (name, address etc) is on a separate line. 
    In word, you can turn this into a table quite easily using the "Convert to table" command.In word 2007 or 2010:

    - Select the text
    - Click the Insert tab 
    - Choose Table=>Convert Text to Table

    In earlier versions of Word the command appears in the "Table" menu under "Convert".

    You may need to to a bit of "cleaning up" after the conversion if some items are missing from some records (e.g. there's no address for some names).

    Once you have a table, you can copy it between Word and Excel and its "tableness" will be preserved.

  2. ha14
    April 11, 2012 at 1:23 pm

    Change Horizontal Data to Vertical - Excel TRANSPOSE Function

    Perhaps Bruce Epper can write the script for you, some heavy transformation needs careful action.

  3. Bruce Epper
    April 11, 2012 at 10:32 am

    Can you give a specific example of what you are starting with and what you want the final result to look like?  Are all initial cells set up with the name on line one of the cell and the entire address on line 2 with no other lines in the cell?  Do you only want a result of 2 columns with the first column being the first line of the original cell and the second column being the second line of the original cell?  Do any of the lines need to be broken out into additional columns?  Attach before and after images to clarify this better if you need to.  If I know EXACTLY what you are looking for, I can write a VBA script to handle the task for you.

  4. Suemc33
    April 11, 2012 at 12:40 am

    The easiest way is to be sure you have an empty column to transfer the addresses to first. Then select the row, copy it, and then place your cursor in the first cell of the column they are to go into. Right click next and choose "paste special". On the pop up screen toward the bottom choose "transpose", and the addresses will fill the column going down versus across. You may have to do this for each row, but it is much faster than redoing the whole sheet.

    • Bruce Epper
      April 11, 2012 at 10:37 am

      All this process will do is take a row or column of data and turn it into a column or row of data respectively.  It does not take individual lines from a multi-line cell and break it out into individual columns for each cell in a speciic initial column which is what he appears to be asking for.