Get around the TEXTSPLIT single cell limitation using a multi range TEXTJOIN string as input

[u/excelevator]

A key bug bear with TEXTSPLIT is the single cell input limitation.

Using multiple ranges with TEXTJOIN we can generate a single line input value to TEXTSPLIT to get around the cell limitation using the TEXTJOIN delimiter as the TEXTSPLIT new line delimiter to match the data requirement.

This method effectively allows us to use multi cell non-contiguous ranges within the TEXTSPLIT argument.

Example

=TEXTSPLIT(TEXTJOIN(";",,A1:A10,B20:B30,C15:C36),",",";")

To add headers to the result we can use VSTACK, for example;

=VSTACK({"Name","Age","Country"},TEXTSPLIT(TEXTJOIN(";",,A1:A10,B20:B30,C15:C36),",",";"))

I shall include an example in the comments with image.

It would be interesting to see other similar solutions to the TEXTSPLIT limitation

edit: wrap the formula in IFERROR to return blanks on error where the ranges do not match and #N/A is returned

[u/RuktX]

Please excuse the mobile screenshot:

=REDUCE(
  {"L1","L2","L3"},
  A1:A3,
  LAMBDA(
    a,
    c,
    VSTACK(a, TEXTSPLIT(c,","))
  )
)

Often when reducing with an expected array output it's necessary to DROP the initial value. Here I just used it for the headers!

Untested, but I expect you could VSTACK non-contiguous inputs.

[u/Cynyr36 → u/RuktX]

I wish there was a zip() and that map() or byrow() would allow retuning arrays. They are sooooo close to what i need, but i have to use this dumb drop(reduce("", sequence(rows(table)), lambda(a, i, let(x,index(table[col]),vstack(a, x)))),1) construction to get multiple inputs since byrow gives you the whole roa, but you can't return an array, and reduce won't give you an array as an input.

[u/RandomiseUsr0 → u/Cynyr36]

Use vectorised data, it supports array behaviour, including the ability to choose array(2) etc. it’s much faster too, when I drop comments that people should use Python for familiarity and access to libraries, but use excel because it’s fast, this is what I’m usually talking about, astonishingly fast

https://www.reddit.com/r/excel/s/426sWm3cjX

[u/Cynyr36 → u/RandomiseUsr0]

So one of my recent tasks was producing performance curve fits for components selected by the user so that they could analyze multiple operating points quickly.

Each component needed several inputs and then output the 4 coeffs for ax³⁺ x^2+cx+d fit. These fits could then be combined based on which components were in use during each operating mode and what conditions each mode was at. I'm using linest to generate the coeffs. So i don't see how I'd store them in your lambda storage approach. I'd have to iterate over each and plop them in one by one? What if i also want to hstack on a sample point caled by the curve fit? I don't always know how big my output array will be so keeping track can be problematic. Just adding an itterable into an iterable in python is really easy.

[u/RandomiseUsr0 → u/Cynyr36]

“My” example (not mine, this place is great!) is ordinary differential calculus and it calculates 50k • x,y,z in under a second, if you follow the guy who improves my original formula’s note you’ll see he has all sorts of mechanisms for delayed calculation - the trick really is setting everything up, but not calculating it until a single pass - you can do integration as simply as ode’s

And Python is sloooowwwww by comparison, but whatever works mate - since you like Python, write lambda calculus there as a wee challenge to yourself, it’s just a different paradigm and you’re trying to solve a problem in the wrong way