2

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

Improve this question

1 Answer 1

Reset to default
4

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 
        ), 
        " " 
      ) 
    ), 
    "_", " " 
  ) 
)
Improve this answer
4
  • Thank you @Doublenary. This works perfectly. I do want to be able to understand the function. What does the 9^9 stand for?
    – Genny
    Jun 17, 2022 at 7:40
  • 1
    The 9^9 is simply a big number, about 3.87E+08. See query smush.
    – doubleunary
    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.
    – Genny
    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.
    – doubleunary
    Jun 17, 2022 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.