Text to Column with Text & Varying-length Numbers

I am trying to separate a string into two columns, however, the string contains text and numbers and it does not have an easy deliminator.

Here is an example of the string data:
Antioxidant Rich Fruit Blends. 6 months and up4.00 oz
4.22 oz
10.00 oz
Snap on lid 3.5 oz - 2 pack7.00 oz
No artificial flavors. Microwavable.45.00 oz

This is what I'm trying to get it to look like:

Please Login or Register  to view this content.

The only consistent aspect is the "oz" at the end, however, as you can see sometimes "oz" appears in the middle of the string.

I have tried LEFT/RIGHT/Mid, however, since the numbers I want in column B vary in length from 4 characters to 6, LEFT/RIGHT/MID ends up cutting something wrong.

Thanks for your help!

After giving this problem a little space, I was able to figure out a solution.

Please Login or Register  to view this content.

The key to this problem is figuring out where to tell the MID function to start. So, the MIN(FIND function finds the first occurrence of a number 1 through 9, but instead of starting to look for numbers at the beginning of the cell we find the last "oz" and back up 8 characters.

I hope this helps someone.

Hi you can try this and see if it works for you.

Please Login or Register  to view this content.

Select the First cell containing data and run macro "TxtToCol" below.Pl convey your result.
Thanks in advance.

Please Login or Register  to view this content.

Another way: