# Text result of JOIN is longer than the limit of 50000 characters

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.

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

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
),
" "
)
),
"_", " "
)
)
``````
• Thank you @Doublenary. This works perfectly. I do want to be able to understand the function. What does the 9^9 stand for? Jun 17, 2022 at 7:40
• The `9^9` is simply a big number, about `3.87E+08`. See query smush. Jun 17, 2022 at 7:59
• Sorry, I'm still working on the function trying to figure it our. One more thing, when I changed the (Data!B2:B = "paid") to something with numbers "250p" I get #N/A - No matches are found in FILTER evaluation. Actually it could contain text after the search term too, such as 250p paid, or 350p pending. Jun 17, 2022 at 8:02
• To do partial text searches in a `filter()`, use `search()` or `regexmatch()` instead of testing for equality. Please ask only one question per post. Jun 17, 2022 at 8:43