PDA

View Full Version : Solved: parse and new column



mikeo1313
06-05-2010, 05:22 PM
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:

mikeo1313
06-05-2010, 05:28 PM
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??

GTO
06-05-2010, 09:28 PM
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

mdmackillop
06-06-2010, 02:47 AM
This is based on Shred Dude's methodology here (http://www.vbaexpress.com/forum/showthread.php?t=32424). 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.

mikeo1313
06-08-2010, 01:34 PM
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.

mikeo1313
06-08-2010, 01:59 PM
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.