Problem description & analysis

Below is an Excel filebook1.xlsx:

The task is to split A1’s string by comma, format and rearrange it as a table of 5 columns and N rows with column headers. The data will be populated by row first and by column later, as shown below:

Solution & explanation

Write the following scriptp1.dfxin esProc:

Explanation:

A1 Import data from clipboard and split the string into a sequence by comma.

A2 For each member in A1’s sequence, the sequence number minus 1 and then divided the result by 5. If the remainder is 0, create a new group to generate a sequence of sequences. Then convert the sequence into a string (rows are separated by carriage return and columns are separated by tab).

After the program is executed, select cell A2 in esProc and click the corresponding “copy data” button on the right. Back to Excel to click cell B2 and then press Ctrl+V to paste the result in.

Q & A Collection

https://stackoverflow.com/questions/63747031/very-long-csv-data-in-excel-single-cell