How to separate payment product data into separate excel columns

  • eaglestfxc
    Asked on November 12, 2013 at 2:15 PM

    Submission data from payment widget for products is lumped into 1 column with no commom delimiter. I need a common delimiter between product name, amount, and size (a custom property) then the same delimiter separating 1 product's information from the next product. I cannot parse in excel without it. I need to count number of items ordered of each type and each size.

     

     My form is  http://form.jotform.us/jsform/33145720244143"

  • Elton Support Team Lead
    Replied on November 12, 2013 at 3:26 PM

    Hi,

    Unfortunately we do not have that option in the form builder. Products are exported in a single column just how it was arranged on your form submissions page.

    How to separate payment product data into separate excel columns Image 1 Screenshot 40

    However, this workaround will probably help.

    In a column adjacent to the column with the product listings (if the product listings are in A1-A13) insert the following formula, let says in G1 (or any other free column),

    =SUBSTITUTE(A1,CHAR(10),"^")

    Copy this formula down to G13.
This will copy the data in A1 through 13 into the corresponding cells in A. Now highlight the data in A1-A13 and copy the cells (ctrl+c), after that go to Edit --> Paste Special -> Values then OK or Paste Values. This pastes the actual values of the cell instead of its formula. Now you have a column of data with ^ substituted for every carriage return (end of line).

    Highlight A1-A13, then Data --> Text to Columns. When the Wizard comes up, select Delimited on the first screen and Other on the next screen. In the blank space next to Other, insert ^. Click Finish, and your products should be listed in separate columns. 

    How to separate payment product data into separate excel columns Image 2 Screenshot 51

    How to separate payment product data into separate excel columns Image 3 Screenshot 62

    Hope this helps. Thank you!

  • eaglestfxc
    Replied on November 12, 2013 at 3:46 PM

    This is not working for me. Although all products display on spreadsheet, only the first item shows up in the formula bar.  the substitute functions show all results but when I do the text to columns, only the first product is shown and the othere products disappears.. 

  • eaglestfxc
    Replied on November 12, 2013 at 3:47 PM

    Also, when I generate a CSV file, I only get the first product data and none of the others.

  • eaglestfxc
    Replied on November 12, 2013 at 4:54 PM

    Also, when I generate a CSV file, I only get the first product data and none of the others.

  • jonathan
    Replied on November 12, 2013 at 7:10 PM

    Hi,

    Can you confirm if this is the form http://form.jotform.us/form/33145720244143 involve. 

    I will contact our colleague EltonCris with regards to your latest message since he provided the workaround he knows that should be working -- and apparently it did not on your end.

    And I also would like to help out on a resolution the reason I need to get the righ form's submission to test.

    Thanks.

  • eaglestfxc
    Replied on November 12, 2013 at 7:31 PM

    Yes that is the correct form.  The workaround didn't work.  Also, when I pull my form submissions down in a csv format only the first product's information shows up.  The other product data is missing.

  • Elton Support Team Lead
    Replied on November 13, 2013 at 3:26 AM

    @eaglestfxc

    Thanks. Let me play around with your data so I can come up with an example or I can work with the whole data. Unfortunately this is the only way to achieve this as of the moment. I'll get back to you with updates about this later today.

    Regarding on the other issue about CSV, may we request you to open it on a new thread so we can help you focusly about the workaround on spreadsheet in this thread. Sorry but we cannot handle multiple questions per thread. Thanks for your understanding!

    Stay tuned!

  • eaglestfxc
    Replied on November 13, 2013 at 6:45 AM

    I have created a new thread for the CSV problem.  I put them together because there is a good chance that the same bug is causing both problems.

  • eaglestfxc
    Replied on November 13, 2013 at 6:47 AM

    The problem that I was referring to in my last post was the dropping of data after the first product-in Excel after the Substitute function output is copied and pasted (values) and the csv file.

  • Elton Support Team Lead
    Replied on November 13, 2013 at 7:09 AM

    @eaglestfxc 

    Hi,

    For some reason the char13 on formula above is no longer working with excel, kindly update CHAR(13) to CHAR(10), this should fixed the other products from disappearing. Here's the formula now.

    =SUBSTITUTE(A1,CHAR(10),"^")

    Here, I come up with a video tutorial. Check this: http://screencast.com/t/DWOvVgveXM4

    Hope this helps. Thank you and apologies for the delay.

  • eaglestfxc
    Replied on November 13, 2013 at 1:57 PM

    That worked.  Thanks for the video.

  • Elton Support Team Lead
    Replied on November 13, 2013 at 2:52 PM

    @eaglestfxc

    Happy to hear it!

    Cheers!