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

user_9393218 0
Viewed 38

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

transpose google-sheets split
2 Answers

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),","))
user_186453320

To avoid any limitations on string length:

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

enter image description here

user_38949170
Related