How to separate payment product data into separate excel columns

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"

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. 

Hope this helps. Thank you!

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.. 

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

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

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.

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.

@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!

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.

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.

@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.

That worked.  Thanks for the video.

@eaglestfxc

Happy to hear it!

Cheers!