Split text data into one column for several rows in Google Sheets/Excel-CodePudding

So I have data as follows:

text
A,B,C
A
B,D

In Google Sheets, I would like to take each of the rows above, parse by the , and make each entry a row.

So final outcome looks like this:

A
B
C
A
B
D

So here is what I have right now:

=TRANSPOSE(SPLIT(B1, ","))

And this will result in:

A
B
C

But I need to do this for all rows in one column..... so how can I replicate the above formula to make each of these entries its own row in one column

CodePudding user response：

Given the strings to be concatenated in B1 downwards (as your example suggests), you can simply JOIN the individual cells with commas then SPLIT the lot:

=transpose(split(join(",",B1:B),","))

CodePudding user response：

To avoid any limitations on string length:

=ArrayFormula(query(flatten(iferror(split(A2:A,","))),"where Col1 is not null"))