Formula help on comma separated String data?

**JGTExcel** · 02-08-2012, 09:50 PM

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

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.
http://www.msofficeforums.com/excel/...n-formula.html
http://www.mrexcel.com/forum/showthread.php?t=612035
http://www.excelforum.com/excel-gene...12#post2704112

**rylo** · 02-08-2012, 11:11 PM

Hi

This comes close, but not quite there. It doesn't replace any missing descriptions and the butter item seems to have a slightly different construct.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,") [",")|"),"] (","|"),"g carb)",""),", ", "^")

To get closer, and with the different constructs, you may have to go to a VBA solution (in the form of a UDF).

Is this a viable option? If so, can you attach an example file with multiple examples that cover the situations you meet, and the expected output for them.

rylo

**JGTExcel** · 02-09-2012, 09:26 AM

Gives me great Idea's to try. I am trying to get the following result after conversion:
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]

**rylo** · 02-09-2012, 05:35 PM

Hi

As I said in the previous post, I think that you will have to go to a VBA solution, especially with the change of the order, and the different text construct mixes that you have.

Some people / areas do not want / or do not allow macro solutions. Is this a viable option for you to persue?

rylo

**macropod** · 02-10-2012, 01:24 AM

See my reply at: http://www.msofficeforums.com/excel/...n-formula.html

**JGTExcel** · 02-10-2012, 12:05 PM

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…

Once again thanks in advance
JGTeXcel