Multiple values in a single cell

[gilteetoo]

Hi Everyone,

Newbie here

I regularly receive a spreadsheet which include merchandise orders in a column. However, if two or more items are ordered by a customer, the data is all added into a single cell in the row corresponding to their name. I then need to extract the data for individual items, which may include not only a number but also size (e.g. socks 2, shorts 1 L, singlet 1 M) to enable an order to be sent to the manufacturer.

Can anyone help with a solution for extraction the data into individual orders that can then be used to create an order.

Thanks

 

[DSCfromCFA]

G'day Gil,

I have done some playing around with Text to Columns, and it doesn't like the internal carriage return - I am using Excel 2010 - and it doesn't seem to allow it to be used as a delimiter.

To my inexperienced eye, this really needs a VBA solution, and I suspect that with the variable number of lines in the cell, the code is going to be quite complex, particularly as each order line can vary depending on whether a size is included or not.

Is there any chance of getting the source modified - it may ultimately be easier?

shane

 

[DSCfromCFA]

G'day again,

Based on a response in another thread to an inquiry similar to yours, I downloaded the Power Query Add-in for Excel and was able to split the three lines you gave into rows and then split the rows into the 4 blocks of information and pass it back into the worksheet. It seems that you can run that as a routine like a macro, so it may be one way to semi automate the process.

Power Query is an official Microsoft product, so should be consistent with IT security requirements.

I hope that is of use to you.

Good luck

shane