Strange behaviour with TEXTSPLIT which to me seems like a bug. Any other explanations?

a=1,b=2

Formula

Output

Expected

Status

Comment

=LET(
kvs, TEXTSPLIT($B$1,","),
out, APPLYCOL(kvs, LAMBDA(kv, TEXTSPLIT(kv,"="))),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

Why doesn't this work?

=LET(
kvs, TEXTSPLIT($B$1,","),
out, APPLYCOL(kvs, LAMBDA(kv,TEXTSPLIT(kv,,"="))),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

Or at least this?

=LET(
kvs, TEXTSPLIT($B$1,","),
out, APPLYCOL(kvs, LAMBDA(kv, TOCOL(TEXTSPLIT(kv,"=")))),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

Or this?

=LET(
out, TRANSPOSE(TEXTSPLIT($B$1,"=",",")),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

This works but doesn't scale to more rows.

=LET(
kvs, {"a=1","b=2"},
out, VSTACK(TEXTBEFORE(kvs,"="),TEXTAFTER(kvs,"=")),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

This works and led me to discover the next one.

=LET(
kvs, TEXTSPLIT($B$1,","),
out, VSTACK(TEXTBEFORE(kvs,"="),TEXTAFTER(kvs,"=")),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

Bizarrely this works!

=LET(
kvs, TEXTSPLIT($B$1,","),
out, TEXTSPLIT(kvs,,"="),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

But this doesn't.

=LET(
kvs, HSTACK(TEXTBEFORE($B$1,","),TEXTAFTER($B$1,",")),
out, TEXTSPLIT(kvs,,"="),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

Why doesn’t this work even though we're splitting by row delimiter?

=LET(
kvs, HSTACK(TEXTBEFORE($B$1,","),TEXTAFTER($B$1,",")),
out, APPLYCOL(kvs, LAMBDA(kv, TEXTSPLIT(kv,,"="))),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

Why doesn’t this work even though we're splitting by row delimiter?

=LET(
kvs, HSTACK(TEXTBEFORE($B$1,","),TEXTAFTER($B$1,",")),
out, VSTACK(TEXTBEFORE(kvs,"="),TEXTAFTER(kvs,"=")),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

The fact that this works seems to really point the finger at TEXTSPLIT as the problem.

=LET(
kvs, TEXTSPLIT($B$1, ","),
keys, MAP(kvs, LAMBDA(kv, INDEX(TEXTSPLIT(kv, "="), 1))),
vals, MAP(kvs, LAMBDA(kv, INDEX(TEXTSPLIT(kv, "="), 2))),
out, VSTACK(keys,vals),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

Here TEXTSPLIT works again. Why? Because it's inside a MAP?

=LET(
kvs, TEXTSPLIT($B$1,","),
out, APPLYCOL(kvs, LAMBDA(kv, TOCOL(TEXTSPLIT(kv,"=")))),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

Why doesn't this work when the previous line worked?

=LET(
kvs, TEXTSPLIT($B$1,","),
out, APPLYCOL(kvs, LAMBDA(kv, VSTACK(TEXTBEFORE(kv,"="),TEXTAFTER(kv,"=")))),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

=LET(
out, APPLYCOL({"a=1","b=2"}, LAMBDA(kv, VSTACK(TEXTBEFORE(kv,"="),TEXTAFTER(kv,"=")))),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

=LET(
kvs, TEXTSPLIT($B$1,","),
out, APPLYCOL(kvs, LAMBDA(kv,TOCOL( HSTACK(TEXTBEFORE(kv,"="),TEXTAFTER(kv,"="))))),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

=LET(
kvs, TEXTSPLIT($B$1,","),
out, MAKEARRAY(2, 2, LAMBDA(r,c, INDEX(TEXTSPLIT(INDEX(kvs,c),"="),r))),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

Works as expected.

=LET(
kvs, TEXTSPLIT($B$1,","),
txts, MAP(kvs, LAMBDA(kv, ARRAYTOTEXT(TEXTSPLIT(kv,,"="), 0))),
xmlsplit, LAMBDA(text,delimiter, FILTERXML("<t><s>" & SUBSTITUTE(text, delimiter, "</s><s>") & "</s></t>", "//s")),
out, APPLYCOL(txts, LAMBDA(txt, ARRAYTOTEXT(xmlsplit(txt, ", "),0))),
ARRAYTOTEXT(out,1))

{"a","b"}

{"a","b";"1","2"}

FAIL

I'm surprised even this doesn't work.

=LET(
kvs, HSTACK(TEXTBEFORE($B$1,","),TEXTAFTER($B$1,",")),
out, APPLYCOL(kvs, LAMBDA(kv, VSTACK(TEXTBEFORE(kv,"="),TEXTAFTER(kv,"=")))),
ARRAYTOTEXT(out,1))

{"a","b";"1","2"}

{"a","b";"1","2"}

PASS

=LET(
kvs, TEXTSPLIT($B$1, ","),
out, APPLYCOL(kvs, LAMBDA(kv, COUNTA(TEXTSPLIT(kv, "=")))),
ARRAYTOTEXT(out,1))

{1,1}

{2,2}

FAIL

It's weird that with APPLYCOL the COUNTA is only seing a 1-element array.

=LET(
kvs, TEXTSPLIT($B$1, ","),
out, MAP(kvs, LAMBDA(kv, COUNTA(TEXTSPLIT(kv, "=")))),
ARRAYTOTEXT(out,1))

{2,2}

{2,2}

PASS

With MAP, COUNTA sees a 2-element array as expected.