I have a long, irregularly formatted text string field that looks like this:
Cake ingredients: flour, sugar, milk, eggs. Contains: milk; wheat Frosting ingredients: sugar, butter, water, food coloring Allergen alert: contains milk. Conventional oven: preheat to 350 degrees. Bake for 40 minutes. Product of the USA.
I need to parse ingredients, allergens, preparation instructions, country of origin, and a few other fields, then re-concatenate like fields (ingredients with ingredients, allergens with allergens) in the same order in which they appear. The resulting text would appear like this:
[Ingredients:] Cake ingredients: flour, sugar, milk, eggs. Frosting ingredients: sugar, butter, water, food coloring
[Warnings:] Allergen alert: contains milk. Contains: milk; wheat
[Instructions:] Conventional oven: preheat to 350 degrees. Bake for 40 minutes.
[Country of Origin:] Product of the USA.
I have figured out how to parse the values and concatenate them back together, but I can't figure out how to concatenate the values in a pre-defined order. First, I look for keywords in the string and insert delimiters, then I parse the string into multiple rows, then I parse each value into two fields--one containing the data type (e.g., ingredients, warnings, etc.). I can then use the Summarize tool to concatenate all values with the same data type together, or I can do this step in the Cross-Tab tool, but the fields are not being combined in the correct order. (Note: I'm not performing any other operations between these parse and cross-tab steps.) I think I need to somehow insert the sort during my first parsing step, but I can't figure out how to do this.
