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]

The ease of this will depend if there is a delimiter between items that is used consistently. Typically it is a comma, as you have shown above, if however it is just a space, then it is more tedious to tease out the individual items.

You also need to say whether you want the result in columns or in rows, and the maximum number of items that are likley to be presented this way is also useful information that will assist those who may be looking to assist you.


Cheers

shane

 

[DanteAmor]

Review this

https://support.office.com/en-us/ar...s-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

 

[gilteetoo]

The ease of this will depend if there is a delimiter between items that is used consistently. Typically it is a comma, as you have shown above, if however it is just a space, then it is more tedious to tease out the individual items.

You also need to say whether you want the result in columns or in rows, and the maximum number of items that are likley to be presented this way is also useful information that will assist those who may be looking to assist you.


Cheers

shane

Thanks Shane,

There could be up to 7 individual items and I would like the results in rows. An example of the data is:
[FONT=&quot]- ID: 4699cd9be3519a0be, Name: Club Socks, Price: $10.00, Qty: 1[/FONT]
[FONT=&quot]- ID: 3041b4ad36c36d03c, Name: Club Shorts S, Price: $25.00, Qty: 1[/FONT]
[FONT=&quot]- ID: bca8ee91151c75dad, Name: Dolphins Hoodie S, Price: $60.00, Qty: 1[/FONT]

Hope this helps

 

[DSCfromCFA]

To a certain extent, but for clarity, is it that those three lines would have all been presented that way in a single cell, or is it all just one big long line that has the separators you indicate, which are:
hyphens at the beginning of a new item,
colons to indicate a fixed text field, and
commas to indicate the variable alphanumeric fields

I know this sounds like I am being very pedantic, but in order to extract exactly what you want, we have to know exactly what we are starting with.

Cheers

shane

 

[gilteetoo]

Hi Shane, yes that is how the data is presented. There is a carriage return within the cell when each extra item is included.
Cheers, Gil

 

[DSCfromCFA]

Thanks Gil,

The Text to Columns function is how it is done manually, but you would likely need to do it in two steps, first to extract each line based on the carriage return character, then if required, break up each line into its four component parts,where the colons and commas are.

The next method would be to use the Left, Mid and Right text functions to extract each component, but that would be quite a task, based on up to seven lines needing to be extracted and broken up.

The easiest way for it to be done is probably with VBA and for that you need a VBA guru - definitely not me - that will be able to help you - although you could search this forum for something like "vba text to columns" for some ideas.

 

[gilteetoo]

Review this

https://support.office.com/en-us/ar...s-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Thanks Dante

 

[gilteetoo]

Thanks Shan, will give it a try

 

[DanteAmor]

You're welcome.