Formatting/Organising data from a generated CSV

Hi all,

I'm looking to find a way to arrange the info on the Before sheet in to something like it's shown on the After sheet. I've taken a crack at this a few times but the furthest I've managed to get is separating each comma delineated item into separate cells but that's all. The quantity of each length still stays in the same cell this way which isn't useful for what I need and the "mm" and brackets also still remain.

This is a small example of what I usually have to work with and I've been getting by doing this manually so far. Is there a way to separate this out?

Please Login or Register  to view this content.

If this presentation works for you then it can be achieved with Power Query

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
16	Length	Deck Length Hollow	Deck Length Solid	3"x3" Post	6"x2" Treated Timber
17	4200				2
18	3600	3	3		2
19	3000	6	2		2
20	2400	2	1		2
21	1800	1	2		3
22	1588	4	2		4
23	1288	2	4		4
24	1200	1	2	10	2
25	988		4		10
26	900	4	2		4
27	600	4	5		4
28	300		4		51

Sheet: After

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Please Login or Register  to view this content.