Solved: parse and new column

I have an excel for recipes I recently imported into an access database

heres how the nutrition data field looks :

84 Calories; 3g Fat (36.5% calories from fat); 1g Protein; 9g Carbohydrate; 1g Dietary Fiber; trace Cholesterol; 81mg Sodium. Exchanges: 0 Grain(Starch); 1/2 Vegetable; 1/2 Fruit; 1/2 Fat.

I've been tinkering with vba for the last couple weeks but I haven't come across a way that I can parse data in this field and separate calories, fat, protein, carbs, etc etc into separate fields.

any suggestions, I'm stuck. I don't care if the suggestion works for excel or access, I just want the data to be separated so I can perform & categorize further. Thanks : pray2:

I was thinking maybe I can just copy the nutrition data field to a separate excel file. Then perform multiple search & replaces for the areas I can identify a separation of data i.e. "calories; " "fat (" for "||" , export to a csv file, then bring the csv back using || as a delimiter to then paste that back into my original file. How would you go about this??

If this string:

"84 Calories; 3g Fat (36.5% calories from fat); 1g Protein; 9g Carbohydrate; 1g Dietary Fiber; trace Cholesterol; 81mg Sodium. Exchanges: 0 Grain(Starch); 1/2 Vegetable; 1/2 Fruit; 1/2 Fat."

Is all in one cell, could you attach a workbook (.xls format) with several different examples as well as what the several After's should layout like.

Maybe I am missing something, but the parts like "(36.5% calories from fat)" would seem problematic for an easy text-to-columns. If we could see a good representation of input data and expected parsing, maybe a regular expression or ??? would fix.

Mark

This is based on Shred Dude's methodology here. Needs a bit more work but does it help?. The Sodium value fails due to the ".", but such anomolies are to be expected and should not be insurmountable.
You're not clear what the "final" result should be, and that might need further resolution.

I ended up doing a search & replace as described I was kinda in a rush to get this over with and finished within the hour.

I dl'd food.xls, when I tried changing the value of fat the cell got name#,, and stayed the same even when I retyped fat. None the less, a great function. Looks quite complicated.