Excel Formatting

New Contributor

First time on this feature so hoping someone can help me or point me in the right direction.


I have imported the information contained on a CSV file from a supplier onto a "Variations" spread sheet created in excel. 

I now need to extract the various colours from the column and place them in a cell on a separate "Products Spreadsheet"



18001 Dark green Small

18001 Dark Green Medium

18001 Dark Green Large

18001 Blue Small

18001 Blue Medium

18001 Blue large


Number in Column A

Colour Column B


I need to capture these size variations in one cell on a "Products sheet" so S I M I L (with a vertical bar between) This is so that I can upload onto my web site.


Trouble is I don't know where to start to find the formula to write into the cell to get the information across


Any help would be greatly appreciated. 


5 Replies

@A1anBaugh I suggest you look into "Get & Transform Date" (a.k.a. Power Query) on the Data ribbon. You can opt to connect your supplier's CSV file (or to the table that you already imported into your Excel sheet) and transform the data into the columns you need. If the structure of the data is really as consistent as you suggest, it's not very difficult. No complicated formulae required.


Are you familiar with Power Query?


Power Query could be an option, it looks like


In any case it's better if you provide sample file and indicate on which version of Excel you are.



Thank you for your help I am not familiar with Power Query - but I will now explore that feature.

@Sergei Baklan 


Hello Sergei

Thank you for your response I will look into this feature. I am using excel 2016.




Power Query is built-in into 2016. However, if that's not an option, formula solution without dynamic arrays will be bit complicated. As variant that's to create helper columns with color and size, after that PivotTable.