Separate number into their own cells, but more advanced.

[gleemonex69]

Good morning, Excel gods. I need help yet again. I have a lot of data in on cell that I need separated into their own cells.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]866-0108 1/15 LB BONGARDS BLOCK YELLOW CHEDDAR 2.10 2.13 .03- 87.00 109.62-[/TD]
[/TR]
</tbody>[/TABLE]



This is what is in one cell.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]866-0108[/TD]
[TD](Left Blank)[/TD]
[TD]1/15 LB[/TD]
[TD]BONGARDS[/TD]
[TD]BLOCK YELLOW CHEDDAR[/TD]
[TD]2.10[/TD]
[TD]2.13[/TD]
[TD].03-[/TD]
[TD]87.00[/TD]
[TD]109.62-[/TD]
[/TR]
</tbody>[/TABLE]




This is the outcome I would like. Thank you so much for your assistance.

 

[Joe4]

It depends on what your other data looks like and how much variance there is.
Can you come up with an explanation of exactly where every item should be split that will work on ALL your data?
If not, I don't think you will be able to come up with a formula to do it.

Excel is not magic, it cannot do that which cannot be explained. It can only do exactly what we tell it.
So, in order to program a rule, we need to know exactly that rule is.

 

[gleemonex69]

Thank you so much for replying, but I forgot that Excel has a Text To Columns feature. I got it squared away. Thank you.

 

[Joe4]

How do you account for some of your entries having more than one word, as if you use Text to Columns and use the Space as a delimiter, entries like:
1/15 LB
and
BLOCK YELLOW CHEDDAR
will actually be split over multiple cells, and not all in one cell each?

 

[gleemonex69]

Text to Columns > Select Fixed Width > Next > then move the break lines to set fields widths > Next > Finish.

Separated 680 rows really quick.

 

[Joe4]

OK. Didn't realize that your records were all aligned vertically, where "Fixed Width" would work (as we really cannot tell that by just seeing a single record).

Yes, Text to Columns is a great tool that works under the right conditions (data is all aligned or all delimited by a specified character).
Glad your data is aligned in such a way that allows you to use it. It certainly makes the task pretty straightforward. If your data wasn't aligned in that manner, this would prove to be quite a tricky task.