parse & separate

[mikeo1313]

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 access & vba for the last week and quite a bit 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

 

[PNGBill]

Have you tried to extract ; 9g Carbohydrate
Look for ; and what comes after it that includes Carbohydrate

 

[mikeo1313]

I was thinking about working with the excel file, copying a # column & nutritional data to a separate file, exporting to a csv, opening it back in excel with the ; delimiter and then adding the ; separated data back. Its all I can imagine to do that would work.

That also seems like all I can do unless there were a command to replace ; with a new table field command to be processed only within a single table field.

 

[PNGBill]

Here is a link to Find and Replace

http://support.microsoft.com/kb/208923

But I thought also that excel may do the task easier.

On a copy, replace everything with ??? Carbohydrate (if ? is the chr symbol) and repeat on copies until you have all the different headings.

You may have to edit out some leading spaces when one value is 12.3 and another 3.3 the 3.3 may have a space as the first chr.

Paste the individual results into your new excel spreadsheet and import this into access.

 

[PNGBill]

If you don't have all records included in all options then keep the heading with each of your excel results and you can import these and use an update query to put the value into the access table of each matching record as you run through the different fields.

 

[mikeo1313]

thanks for the link, I did it in excel.

 

[gemma-the-husky]

use the split function, based on the ; character - then go from there