1

A few days ago I reach the limit of 50000 characters in one cell, I found a solution with "query" but I need a delimiter inside the formula that I haven't been able to find:

This is the formula:

=arrayformula(query(Sort!A2:A,,10000000))

as an example I used this A, B and C

with =TEXTJOIN("|",TRUE,arrayformula(A2:A)) I get:

A|B|C

but with =arrayformula(query(A2:A,,10000000)) I get:

A B C

How can I make the delimiter?

1
  • Might be valuable to explain why you need text strings with >50,000 characters in your cells. Might be an The XY Problem that we can help with.
    – Blindspots
    Jul 9, 2023 at 17:57

1 Answer 1

0

Use regexreplace() and substitute() with query smush like this:

=arrayformula( regexreplace( substitute( substitute( query( substitute(A2:A, " ", "µ"), "", 9^9), " ", "|" ), "µ", " " ), "\|+$", "" ) )

If your data does not contain spaces, you can leave out the inner and outer substitute().

Your Answer

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

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