I want to split a column of data, where there are multiple lines in a single cell. I want these split into rows with a formula.
I've got the JOIN formula set up for that and it works perfectly when I tested with just a few rows. But I have around 200 rows of data and this formula gives me the error Text result of JOIN is longer than the limit of 50000 characters.
I get the same error with TEXTJOIN
=TRANSPOSE(SPLIT(JOIN(CHAR(10),Data!C2:C),CHAR(10)))
Is there anyway I can fix this, to bypass the limit?
I've looked at CONCATENATE too but I need to join cells with a delimiter so I can split later. And query function, I couldn't figure out if its possible with that.
Result format
Basically the multiline cells split into separate rows. And to further explain my purpose, I don't require all the cells, just only the cells where the value is either paid/pending/not paid, I'll be putting these 3 into separate sheets. I have the formulas for filtering this too, but the block is this limit of 50000 characters