Separating purchased "products" in Excel export of submission data

  • Profile Image
    Asked on February 03, 2012 at 04:38 PM

    This is a followup to a comment I added on the question that a user asked here regarding coallating submissions.

    The form I am working on is collecting registrations and payment for an event.  There are several options to register for, and several add-ons to purchase.  

    The submission data groups all items, qty, and price into one cell, making it difficult to tally anything up when multiple quantities are purchased.  It was suggested to use conditional formatting, but that doesn't help much as it doesn't do anything to account for multiple qty ordered. 

    Hopefully in the future this data will be seperated into seperate columns for easy calculation and sales tracking, but I was looking for any tips on how to handle this in the mean time.

  • Profile Image
    Answered on February 03, 2012 at 07:42 PM

    Thank you for contacting us.

    I am sorry to inform you, but according to this thread the workaround is not available.

    However, separate payment data feature request has been already assigned to one of our developers and we hope it will be implemented in the near future.

    We will update this thread once the feature will be available.

  • Profile Image
    Answered on March 08, 2013 at 11:36 AM

    Has there been an update on this? I am in a similar situation.

  • Profile Image
    Answered on April 22, 2013 at 12:05 PM

    If Jotform would append a "non-regular" character e.g ^,&, or @ after each purchased product, then Excel would be able to separate the fields. Now there is no character delimiter, although on expansion, the long line separates into separate lines within the cell.

    The following example displays as one long line in Excel. Double clicking and hitting Enter produces the following display. Note an end of line, hidden (CR),  must follow each field delimiter. Excel will not recognize this character in switching text to columns

    I've paid my 2012-2013 Membership Dues  (Amount: 0.00 USD)
    Spring Semester Registration (Amount: 35.00 USD)
    Total: $35.00

  • Profile Image
    Answered on April 23, 2013 at 08:14 AM

    After a bit of searching, I've found a workaround for this problem.

    In a column adjacent to the column with the product listings (if the product listings are in AK1-AK13) insert the following formula in AL1 (or any other free column) =SUBSTITUTE(AK1,CHAR(13),"^") Copy this formula down to AL13.

    This will copy the data in AK1 through 13 into the corresponding cells in AL. Now higlight the data in AL1-13 and Edit->Copy the cells. Do not move the cursor, then Edit-->Paste Special, Values, OK. This pastes the text over the formula. Now you have a column of data with ^ substituted for every carriage return (end of line). 

    Highlight AL1-13, 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 ^. Now go to Finish, and your products should be listed in separate columns.