Tricky formula needed to format multiple columns into one cell

**joejnknsn** · 08-17-2012, 04:25 PM

For example all of the following to the left of the : are the header of a column and to the right of the : are the data in one single cell.

Size: 5,6,7,8,9,10
Quantity: 11,22,33,44,55,66
Price Increase: 0,0,0,0,0,0
Additional Weight: 0,0,0,0,0,0
Sort Order: 1,2,3,4,5,6

How would you format this into a new column/cell to display it as the following which is SIZE:QUANTITY:PRICE INCREASE:ADDITIONAL WEIGHT:SORT ORDER|SIZE:QUANTITY:PRICE INCREASE:ADDITIONAL WEIGHT:SORT ORDER and so on...

5:11:0:0:1|6:22:0:0:2|7:33:0:0:3|8:44:0:0:4|9:55:0:0:5|10:66:0:0:6

I am at a loss. Look forward to receiving your help.

**shg** · 08-17-2012, 04:51 PM

Insert a column to the left of the data

Copy the header cells and transpose to the inserted column

Select the data column, Data > Text to columns, select Delimited, and tick Comma as the delimiter.

Please Login or Register  to view this content.

**joejnknsn** · 08-17-2012, 05:54 PM

Thanks for the reply but my data will be for thousands of products not just one data set.

Also the final output I am looking for in one single cell is the following:

5:11:0:0:1|6:22:0:0:2|7:33:0:0:3|8:44:0:0:4|9:55:0:0:5|10:66:0:0:6