Separate the submission data on Excel exported data

Hello, Is there a way to better separate to data within the excel export. Current result below

[Quantity: 15, Month: 7, Year: 19Quantity: 8, Month: 6, Year: 19]

Currently no separation of the two sets of data

Unfortunately this how the exported to Excel data of configurable list widget work for now.

Because the widget is considered a single field, all the data in the widget will be contained also on within a single column/cell in the Excel submission data.

The option you can do is to separate the data using your Excel application. You can use the Split text into different columns function in Excel.

Hello,

To expand on Jonathan's post, there is a way to manipulate the report using the new (or newish) Power Query function in Excel. I believe all versions post 2016 have this, including 365.

First off I created an excel report in Jot Form to include the configurable list.

In Excel select the DATA tab from the ribbon. Then Select the left option on the ribbon, GET DATA. Then select From Other Sources/From Web.

Then paste in the Report URL from Jot Form and select OK.

Once complete the data should appear in columnar form.

The issue with the configurable list widget is that it combines all of the data into a single column. However you can use the SPLIT COLUMN function in Excel power Query to split column by special character, line break and then select the option by rows. For example if you have collected 4 sets of configuration list data in the form, splitting the column by row using this method will open up the data set from 1 row in the Excel Power Query Report to 4 rows instead. From here you can then split columns again but this time into columns and not rows to split out the fields/elements contained in and collected in the configurable list. 

What's more because this Power Query report directly links to the URL excel report on Jot Form the manipulated report in Power Query automatically updates in the split column report format, ie you don't have to keep adjusting the excel power query report every time you make new form submissions. The same, I believe happens if you use Power BI. 

The Excel report may need some tweaking here and there but once you have split the rows then the columns you then have a complete data friendly report which, on the back end of this you can add pivot tables, charts, slicers etc.

Because I found this purely through experimentation I have documented and created a guide for this which I am more than happy to pass along as it's sometimes difficult to describe the process just using text here in a forum.

I hope this helps....

PS The URL in Jonathan's post (Split Text Into Different Columns Function) shows the method for splitting the data from a standalone report. The method above links Excel directly to the report setup in Jot Form using the new functionality within Excel, making things super efficient.

Just in case you'd like to try this option for your next forms, you can use Input Table instead. It will create one column in the spreadsheet for each cell of the Input Table. Though note that it cannot give the dynamic number of rows that a configurable list provides.