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.
|
Nou pe simpatie:
inna_90
 | Femeie
23 ani
Vrancea
cauta Barbat
30 - 48 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
TRaPModerator
Postari: 960
|
|
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:
https://www.reddit.com/r/excel/comments ... t/l0601uy/
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;",") |
|
|
| |
|
