How to convert at single colum list into a multi column table

Discussion in 'Excel' started by DanM, Jul 15, 2014.

  1. DanM

    DanM

    Joined:
    Jul 5, 2014
    Messages:
    3
    Likes Received:
    0
    Hi,

    I have a listing (imported from a text file) that looks something like this (no blank rows)

    Animal: Dog
    Color: Brown
    Height: 20 inches
    Name: Spot

    Animal: cat
    Color: white
    Height: 12 inches
    Name: cutey

    Animal: bird
    Color: green
    Height: 6 inches
    Name: butch

    I'd like to get it in a format lie this:
    Animal: Dog cat bird
    Color: Brown white green
    Height: 20 inches 12 inches 6 inches
    Name: Spot cutey butch

    Any Ideas?

    Dan
     
    DanM, Jul 15, 2014
    #1
    1. Advertisements

  2. DanM

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    To save you some typing and typos, you can cut-n-paste these in the appropriate cells:
    =IF(LEFT($A2,LEN(B$1))=B$1,MID($A2,LEN(B$1)+1,22),"")
    =CONCATENATE(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13)​


    DanM1.jpg

    DanM2.jpg
     
    XLPadawan, Jun 13, 2016
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. mary86004

    Excel doesn't filter entire list.

    mary86004, Mar 17, 2012, in forum: Excel
    Replies:
    1
    Views:
    794
    CarolB
    Apr 24, 2012
  2. CarolB

    Advanced Drop Down List Option

    CarolB, Apr 17, 2012, in forum: Excel
    Replies:
    0
    Views:
    707
    CarolB
    Apr 17, 2012
  3. buffLouis

    NA in pivottable list

    buffLouis, Sep 4, 2012, in forum: Excel
    Replies:
    0
    Views:
    591
    buffLouis
    Sep 4, 2012
  4. Jorge
    Replies:
    0
    Views:
    578
    Jorge
    Aug 27, 2013
  5. Jasmeet
    Replies:
    0
    Views:
    717
    Jasmeet
    Jan 26, 2014
  6. Hwalker1
    Replies:
    2
    Views:
    557
    XLPadawan
    Jun 13, 2016
  7. Shivesh
    Replies:
    1
    Views:
    299
    XLPadawan
    Jun 14, 2016
  8. TryllZ
    Replies:
    4
    Views:
    270
    XLPadawan
    Jun 9, 2016
Loading...