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.
Separating purchased "products" in Excel export of submission dataAsked by amsweb1 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.
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.
Has there been an update on this? I am in a similar situation.
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)
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.