How can I export the order data from each submission to create a total for each item ordered across multiple submissions?

I selected the "Google Sheets" integration, but the items ordered were grouped into one big text field:

"Wrist Bands (20 punches each) (Amount: 10.00 USD, Quantity: 1)

Hot Dog Meal (Amount: 7.00 USD, Quantity: 1)

Roasted Corn on the Cob (Amount: 3.00 USD, Quantity: 1)

Jamba Juice (Amount: 3.00 USD, Quantity: 1)

dippin' dots (Amount: 4.00 USD, Quantity: 1)

Total: 27.00"

I want each item in it's own column.  How do I modify the Google Sheets integration to accomplish this?

Regards,

Darren Kikuta

Please note that customizing the output of the Google Spreadsheet integration is not possible from within the form builder. I would suggest creating a copy of that automatically synced sheet, and manually customizing it to fit your needs.

Apologies for not being able to help more.

@pcepto1997

Although what my colleague, Aidan, have mentioned is true, you may still try to do the following workaround below to the spreadsheet integrated with your form to split the data of the ": Products" column.

First, insert a total of 12 columns to the right of the ": Products" column in your spreadsheet. That will be for the 11 products and the total of the payment field.

After that, use the "SPLIT" function to separate the data in the ": Products" column into the columns you just inserted in Step 1.

Select the first empty cell after the ": Product" column and type the following formula:

=SPLIT(text, delimiter)

text — The text to divide (this time it's the cell)

And delimiter — The character or characters to use to split text.

Using the formula, you will have the following for the first submission.

=SPLIT(AE2,"ALT+Enter")

Where ALT+Enter represents a line break.

Note: The products including the total are separated by a new line, so that is why you need to use ALT+Enter as the delimiter in your formula.

http://excelsemipro.com/2010/10/start-a-new-line-inside-a-spreadsheet-cell-in-excel/

Then as for the other submissions, you just have to do the same.

https://www.ablebits.com/office-addins-blog/2016/01/20/copy-formula-excel/

I hope this helps. If you have any further questions or concerns, please feel free to contact us again anytime.

Thank you.