Multiple values in a single cell

gilteetoo

New Member
Joined
Mar 20, 2019
Messages
5
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top