What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    How to separate payment product data into separate excel columns

    Asked by eaglestfxc on November 12, 2013 at 02: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"

    excel separate products
  • Profile Image
    JotForm Support

    Answered by EltonCris on November 12, 2013 at 03: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.

    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. 

    Video Tutorial: http://screencast.com/t/DWOvVgveXM4

    Hope this helps. Thank you!

  • Profile Image

    Answered by eaglestfxc on November 12, 2013 at 03: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.. 

  • Profile Image

    Answered by eaglestfxc on November 12, 2013 at 03:47 PM

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

  • Profile Image

    Answered by eaglestfxc on November 12, 2013 at 04:54 PM

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

  • Profile Image
    JotForm Support

    Answered by jonathan on November 12, 2013 at 07: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.

  • Profile Image

    Answered by eaglestfxc on November 12, 2013 at 07: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.

  • Profile Image
    JotForm Support

    Answered by EltonCris on November 13, 2013 at 03: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!

  • Profile Image

    Answered by eaglestfxc on November 13, 2013 at 06: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.

  • Profile Image

    Answered by eaglestfxc on November 13, 2013 at 06: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.

  • Profile Image
    JotForm Support

    Answered by EltonCris on November 13, 2013 at 07: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.

  • Profile Image

    Answered by eaglestfxc on November 13, 2013 at 01:57 PM

    That worked.  Thanks for the video.

  • Profile Image
    JotForm Support

    Answered by EltonCris on November 13, 2013 at 02:52 PM

    @eaglestfxc

    Happy to hear it!

    Cheers!