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

[DanM]

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

 

[XLPadawan]

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)​