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

Ask a Question

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.