'Separating data by comma and then stacking into one column on Google sheets?
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.
Solution 1:[1]
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)))
Solution 2:[2]
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"))
Solution 3:[3]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Tom Sharpe |
| Solution 2 | player0 |
| Solution 3 |