Separating data by comma and then stacking into one column on Google sheets?-CodePudding

The data is currently all in Column A, with commas separating multiple values in each row. How can I separate the data in each row and then stack them into one column (sorting by the number of values- so the rows with one value will go first in the column, followed by two values, and then rows with three values, etc.)

A1: ibnuaaabbb A2: theresiaaaabbb A3: virnaaaabbb , iskandaraaabbb A4:zakiaaaabbb , vebrinaaaabbb , salsabilaaaabbb , rizkullahaaabbb , rimaaaabbb , noviaaabbb , lanaaaabbb , kintanaaabbb , jhonaaabbb , iskandaraaabbb , ilfaaaabbb A5: afifahaaabbb

Would like to stack into: A1: ibnuaaabbb A2: theresiaaaabbb A3: afifahaaabbb A4: virnaaaabbb A5: iskandaraaabbb A6: zakiaaaabbb A7: vebrinaaaabbb A8: salsabilaaaabbb

etc.

CodePudding user response：

I have got this, but it is probably a bit inefficient:

=ArrayFormula(trim(index(sort(query(split(flatten(len(A:A)-len(substitute(A:A,",","")) 1&"|"&split(A:A,",",true)),"|"),"select Col1,Col2 where Col2 is not null"),1,1),0,2)))

CodePudding user response：

use:

=INDEX(QUERY(TRIM(SPLIT(FLATTEN(SPLIT(A1:A, ",")&"×"&
 TEXT(LEN(REGEXREPLACE(A1:A&"", "[^,]", )), "\¤00000#")), "×")), 
 "select Col1 where Col2 is not null order by Col2"))

CodePudding user response：

Place the following into an EMPTY cell. You cannot put a formula in a cell that contains the data the formula is using.

=transpose(SPLIT(join(",",A:A),","))

This takes all the values in the columns and creates a single string with all values separated by commas.

Then it splits that string into individual columns using the coma as a delimiter.

Finallly, It takes those columns and transposes them into rows.