Splitting a Column

Support Queries Astera Centerprise
1

I am looking to split a column which looks like this:

1 carton sour cream
1 can cream of mushroom soup
2 (16 oz.) pkg. frozen corn
1/3 c. butter
1 (8 oz.) pkg. cream cheese

I would like to split it into:

Quantity, Measurement, Item

For example: 1 | can | Cream of Mushroom Soup

Since all the rows have a different format, I can’t think of a specific delimiter to use. There is no lookup data available with this either.

2

How do I know when the measurement ends and the item begins here? We would need a predefined list of measurements in order for this to work, no? For example, I would need to know that an (8 oz.) pkg. is all part of the measurement token.

If you did have a list of measurements you could do a find and replace pass that would add some kind of delimiter before and after the measurement.