String conversion formula?

JGTExcel · #1 02-08-2012, 08:03 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 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

macropod · #2 02-09-2012, 09:12 PM

Hi JGT,

I'd be inclined to do this in Word, using a Find/Replace macro like:

Code:

Sub ParseMeals()
Application.ScreenUpdating = False
With ActiveDocument
  .Range.InsertBefore vbCr
  With .Content.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchWildcards = True
    .Text = "^t"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
    .Text = "^13{1,}"
    .Replacement.Text = ", ^p"
    .Execute Replace:=wdReplaceAll
    .Text = "([!^13)]{1,}\))*\[([0-9]{1,})*\(([0-9]{1,})(*\), )"
    .Replacement.Text = "^^\2|\3|\1"
    .Execute Replace:=wdReplaceAll
    .Text = "^13^94"
    .Replacement.Text = "^p"
    .Execute Replace:=wdReplaceAll
  End With
  .Paragraphs.First.Range.Delete
  .Characters.Last.Delete
End With
Application.ScreenUpdating = True
End Sub

JGTExcel · #3 02-10-2012, 10:02 AM

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

macropod · #4 02-10-2012, 04:39 PM

Quote:

Originally Posted by JGTExcel

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

See: http://www.gmayor.com/installing_macro.htm

You're unlikely to find any Excel formula that will handle all possibilities. The macro will handle a menu with 100+ items just as easily as it'll handle one with one item.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to extract only numbers from a STRING?	Learner7	Excel	3	07-02-2013 06:25 AM
Export string from e-mail body to .csv	aphrodita	Outlook	0	01-05-2012 08:53 AM
Call Macro from Input Box String	Paulsh	Word VBA	1	11-01-2011 03:15 AM
Extract numbers from a text string	aleale97	Excel	4	02-10-2011 10:33 AM
Extract from String using Wildcard	whousedmy	Word	0	05-21-2009 01:35 AM