Google Sheets – Fix ‘Text Result of JOIN Longer Than 50000 Characters’

formulasgooglegoogle sheetsgoogle-sheets-arrayformula

Sample Sheet

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.

Current format
enter image description here

Result format

enter image description here

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

Best Answer

Use a query smush, like this:

=arrayformula( 
  substitute( 
    transpose( 
      split( 
        query( 
          filter( 
            substitute( substitute(Data!C2:C, " ", "_"), char(10), " " ), 
            (Data!B2:B = "paid") + (Data!B2:B = "not paid")
          ), 
          "", 9^9 
        ), 
        " " 
      ) 
    ), 
    "_", " " 
  ) 
)
Related Topic