Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
christyna1993 25 ani
 | Femeie
25 ani
Bucuresti
cauta Barbat
30 - 44 ani |
|
|
TRaP
Moderator
Inregistrat: acum 7 ani
Postari: 857
|
|
Normally, to split CSV values with TEXTSPLIT you do this: =TEXTSPLIT(A1;","), but since it's a spilled range and you have to either drag the formula down manually for the entirety of your range in column A (which might as well be 150000 rows, or you can use Flash Fill (in the Home tab - Editing group). Either way, it's annoying.
The solution AI will give you it's the obvious =BYROW(A1:A150000;LAMBDA(row;TEXTSPLIT(row;","))) which in theory should work just fine, except it doesn't, with the issue being how BYROW handles the range. I could not understand the exact reason yet.
The solution below is very creative and doesn't use BYROW or LAMBDA at all:
=LET(
rng; A1:A150000;
sep; ",";
IFERROR(
TEXTSPLIT(
TEXTAFTER(
sep & rng & sep;
sep;
SEQUENCE(; MAX(LEN(rng) - LEN(SUBSTITUTE(rng; sep; ""))) + 1)
);
sep);
"")
) |
Source:
LAMBDA function to add to name manager would be:
=LAMBDA(rng;sep;
IFERROR(
TEXTSPLIT(
TEXTAFTER(
sep & rng & sep;
sep;
SEQUENCE(; MAX(LEN(rng) - LEN(SUBSTITUTE(rng; sep; ""))) + 1)
);
sep);
"")
) |
Usage:
| =TEXTSPLIT_RNG(A1:A150000;",") |
|
|
| pus acum 2 zile |
|