Search code examples
excelvbacsvpivot-table

How to take a single long line of delimited values and sort them into a table with 6 columns

I have a lengthy list of values given to me as a long single line with commas to separate the values. I'm trying to organize them into a table with 6 columns (and however many rows it would take to fit all of the values).

For example, the values are given to me in the form:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100

I want to take that list and format it in Excel so that it appears as (and in the numerical ordering as shown):

Column 1 Column 2 Column 3 Column 4 Column 5 Column 6
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15 16 17 18
(....and so on)

I tried saving the values as a .csv file and importing them via Excel with the hope of being able to use Excel's in-built features to format them either via the import feature itself, or by adding them as a table. The best I got was a table with only a single row and 100 columns.

Solution

  • Here is one way of doing in Excel Version MS365, since as per tags posted there are no Excel Constraints then one could use the following:

    enter image description here

    =WRAPROWS(TEXTSPLIT(A1,,","),6,"")

    Or,

    =WRAPROWS(TEXTSPLIT(A1,","),6,"")

    NOTE: This can also be accomplished using POWER QUERY. Also refer that the function TEXTSPLIT() splits the string into Row wise or Column wise and then using WRAPROWS() function converting the one-dimensional array into two-dimensional array by wrapping values into separate rows.