Problem description & analysis

Below is an Excel file data:

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

Use SPL XLL plug-in

Write the formula in B2: =spl("=?.split@c().group((#-1)\5)",A1)

As shown:

Explanation:

Split the string into a sequence by comma.

For each member in A1’s sequence, the sequence number minus 1 and then perform integer divided by 5. If the integer part change, create a new group. That is every 5 numbers are divided into a group.

# means the sequence number, start from 1

\ means get the integer part of the division.

Q & A Collection

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