I am hoping for some assistance. I have Windows 7 Home Premium with Microsoft Office 10, using Excel 2010 v.# 14.0.4734.1000 32-bit. I have a tab delineated text document I have imported into excel. I am trying to then format the data to be imported into another program. The following is an example of one cell that contains some String comma separated information:

Sirloin Beef Patty (1) [2] (0g carb), Golden Potatoes (148g/5.3 oz) [1] (26g carb), Flav-R-Pac Corn (2/3 Cup) [0.6] (10g carb), Arnold Ital. Bread (1 Slice) [1] (15g carb), Butter [1 tbsp] [1] (0g carb), NorthLand Cran-Pom (8oz) [1] (34g carb), Reeses Cups Sm. (5) [0.4] (10g carb)

The result I am looking for is:

1|0|Sirloin Beef Patty (1)^1|26|Golden Potatoes (148g/5.3 oz)^0.6|10|Flav-R-Pac Corn (2/3 Cup)^1|15|Arnold Ital. Bread (1 Slice)^1|0|Butter [1 tbsp]^1|34|NorthLand Cran-Pom (8oz) ^0.4|10|Reeses Cups Sm. (5) [0.4]

The program requests I do the following to properly import the data:

1. X|Y|Z where:

a. X= number of servings
b. Y= number of grams carbohydrate per serving
c. Z= description of the food item (N/A if not available)

2. If multiple food items are included in the meal, the strings above are concatenated and separated by a caret (ASCII 94) character:

a. X|Y|Z^X|Y|Z^X|Y|Z^X|Y|Z

Note that the separators “|” and “^” (pipe and caret) are reserved strings and cannot be found in the data.
In the simplest case, where food item descriptions are not available, a 15 gram meal is represented as: 1|15|N/A

In my data I am dealing with direction #2 multiple food items in the meal string. For instance in the first part of the string in my cell Z= “Sirloin Beef Patty (1)”, X= 2, Y= 0. I need a formula that will look at the cell and convert the data to the import X|Y|Z^X|Y|Z^X|Y|Z^X|Y|Z format.
I have tried nested “IF” statements and think this is a possible way to do it, but can seem to get it right.
What would you suggest is the best way to convert the Data in cell B1 (multiple food items in a meal string) to the import format of X|Y|Z^X|Y|Z^X|Y|Z^X|Y|Z in cell F1? I am hoping for a single formula. Added issue is the formula also has to check cell A1 to see if it is a 5 or not. 5 make the conversion, anything else leave blank.
Thanks in advance and hopeful I explained the issue well enough to get the big brain or the big brains than I to work with.
JGTExcel


P.S. I have edited this post with links to other forums I have also posted to. I was informed that I was breaking rule by posting to several forums with same question. My Mistake.
https://www.msofficeforums.com/excel...n-formula.html
http://www.mrexcel.com/forum/showthread.php?t=612035
http://www.excelforum.com/excel-gene...12#post2704112