String conversion formula?

JGTExcel

New Member
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 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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JGTExcel

New Member
Can't figure out how to edit my post so I adding here.
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]

JGTExcel

New Member
I am just a novice. I don’t even know how to implement that macro in a word document.

I was able to get my anticipated output, with some help from a yahoo answers reply. I used complex, to me, formula in excel after parsing out my example Cell’s comma separated String with Text > Column command and using the following formula.

My Example Cell:
G2= 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)

Parsed out to Text > Column command to Cells N2 - T2 as:
N2=Sirloin Beef Patty (1) [2] (0g carb)
O2=Golden Potatoes (148g/5.3 oz) [1] (26g carb)
P2=Flav-R-Pac Corn (2/3 Cup) [0.6] (10g carb)
Q2=Arnold Ital. Bread (1 Slice) [1] (15g carb)
R2=Butter (1 tbsp) [1] (0g carb)
S2=NorthLand Cran-Pom (8oz) [1] (34g carb)
T2=Reeses Cups Sm. (5) [0.4] (10g carb)

Formulated on in Cell M2 with:
M2=IF(C2=5,CONCATENATE(MID(N2,SEARCH("[",N2)+1,SEARCH("]",N2)-SEARCH("[",N2)-1),"|",MID(N2,SEARCH("] (",N2)+3,SEARCH("g carb",N2)-SEARCH("] (",N2)-3),"|",TRIM(MID(N2,1,SEARCH("[",N2)-1)),"^",MID(O2,SEARCH("[",O2)+1,SEARCH("]",O2)-SEARCH("[",O2)-1),"|",MID(O2,SEARCH("] (",O2)+3,SEARCH("g carb",O2)-SEARCH("] (",O2)-3),"|",TRIM(MID(O2,1,SEARCH("[",O2)-1)),"^",MID(P2,SEARCH("[",P2)+1,SEARCH("]",P2)-SEARCH("[",P2)-1),"|",MID(P2,SEARCH("] (",P2)+3,SEARCH("g carb",P2)-SEARCH("] (",P2)-3),"|",TRIM(MID(P2,1,SEARCH("[",P2)-1)),"^",MID(Q2,SEARCH("[",Q2)+1,SEARCH("]",Q2)-SEARCH("[",Q2)-1),"|",MID(Q2,SEARCH("] (",Q2)+3,SEARCH("g carb",Q2)-SEARCH("] (",Q2)-3),"|",TRIM(MID(Q2,1,SEARCH("[",Q2)-1)),"^",MID(R2,SEARCH("[",R2)+1,SEARCH("]",R2)-SEARCH("[",R2)-1),"|",MID(R2,SEARCH("] (",R2)+3,SEARCH("g carb",R2)-SEARCH("] (",R2)-3),"|",TRIM(MID(R2,1,SEARCH("[",R2)-1)),"^",MID(S2,SEARCH("[",S2)+1,SEARCH("]",S2)-SEARCH("[",S2)-1),"|",MID(S2,SEARCH("] (",S2)+3,SEARCH("g carb",S2)-SEARCH("] (",S2)-3),"|",TRIM(MID(S2,1,SEARCH("[",S2)-1)),"^",MID(T2,SEARCH("[",T2)+1,SEARCH("]",T2)-SEARCH("[",T2)-1),"|",MID(T2,SEARCH("] (",T2)+3,SEARCH("g carb",T2)-SEARCH("] (",T2)-3),"|",TRIM(MID(T2,1,SEARCH("[",T2)-1))),"")

Resulted in:
M2=2|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)

I was very happy with it at first until I tried it on the rest of the cells. This formula only works if the STRING data contains 7 items. Unfortunately each cell to be converted contains 1 to 10 items. Although the before mentioned formula works properly it is limited. Now I am attempting to modify it somehow to adjust for the number of items in the String and not having any luck. I will be doing this import over and over and can’t be changing the formula for each and every Cell in the table every time. Any ideas?

For instance the next cells to be converted contains the follow:
G3=Chips Ahoy (3) [1] (22g carb)
G4= Pizza Hut Pan Meat Lover's (1 pc) [2] (58g carb), Artisan Fresh New York Cheese Cake (1) [1] (45g carb)
Etc…

JGTeXcel