CODEWITHSUNDEEP
google-sheetssplittranspose
John Thomas
John Thomas

Reputation: 1105

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

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

Upvotes: 2

Views: 462

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34230

To avoid any limitations on string length:

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

enter image description here

Upvotes: 0

The God of Biscuits
The God of Biscuits

Reputation: 3177

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

Upvotes: 3

Related Questions