+ Reply to Thread
Results 1 to 4 of 4

String conversion formula?

  1. #1
    Registered User
    Join Date
    02-08-2012
    Location
    Cedar
    MS-Off Ver
    Excel 2010
    Posts
    8

    String conversion formula?

    I am new to forums and couldn't find my post from last night. I thought it was deleted. After reposting this post I located it. I didn't know how to connect the two posts or delete this one so I am just editing this one in hopes by listing my mistake I don't get deleted. Sorry Thanks

    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 reposted this post with links to other forams I have also posted to. I was informed that I was breaking rule by posting to several forums with same question. My Mistake. I couldn't find my original post from last night so I am guessing it was deleted for the same reason.
    http://www.msofficeforums.com/excel/...n-formula.html
    http://www.mrexcel.com/forum/showthread.php?t=612035
    Last edited by JGTExcel; 02-09-2012 at 10:48 AM. Reason: Don't know how to connect posts

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: String conversion formula?

    See my reply at: http://www.msofficeforums.com/excel/...n-formula.html
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: String conversion formula?

    Other posting is here

    rylo

  4. #4
    Registered User
    Join Date
    02-08-2012
    Location
    Cedar
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: String conversion formula?

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1