4

I am testing the following simple case:

=LET(input, {"a,b;c,d;" ; "e,d;f,g;"},
  BYROW(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))

sample excel output

since the TEXTJOIN is the inverse operation of TEXTSPLIT, the output should be the same as input without the last ;, but it doesn't work like that.

If I try using a range instead it works:

sample excel file using range

It works for a single string:

=LET(input, "a,b;c,d;", TEXTJOIN(";",,TEXTSPLIT(input,",",";", TRUE)))

it returns: a,b;c,d

What I am doing wrong here? I think it might be a bug. Per TEXTSPLIT documentation there is no constraint of using TEXTSPLIT combined with BYROW when using an array of strings.

ZygD
  • 22,092
  • 39
  • 79
  • 102
David Leal
  • 6,373
  • 4
  • 29
  • 56
  • 3
    `REDUCE()` can do this trick. See this post. https://stackoverflow.com/questions/73852522/how-to-split-texts-from-dynamic-range – Harun24hr Nov 11 '22 at 02:14
  • Good idea, to overcome this, but why we would need to do this? I was not able to find any documentation that explains that it is not possible to use `BYROW`. Yes, your approach works: `=LET(input, {"a,b;c,d;";"e,d;f,g;"},DROP(REDUCE("", input, LAMBDA(acc,x, VSTACK(acc, TEXTJOIN(";",,TEXTSPLIT(x,",",";", TRUE))))),1))`. I don't think I can use it in my real example, because the input is single string that generates an array. – David Leal Nov 11 '22 at 02:35
  • I was able to make it works in the real example, but still, it is not clear to me why it doesn't work with `BYROW`. – David Leal Nov 11 '22 at 03:11
  • 1
    Interesting. Looks like your choice of semicolons and commas within the string is unfortunate: replacing them with other symbols appears to work fine, for example `LET(input,{"a|b\c|d\";"e|d\f|g\"}`. Since your chosen symbols happen to coincide with the row/column separators for English-language versions of Excel, I can only hypothesize that some buggy internal misinterpretation of these symbols as row/column separators is occurring. – Jos Woolley Nov 11 '22 at 05:36
  • I think it's the same issue that is described here: https://stackoverflow.com/a/73886031/16578424 - see last comment from Scott Cramer. But why don't you use: `=LET(input,{"a,b;c,d;";"e,d;f,g;"}, replaced,SUBSTITUTE(input,",",";"), LEFT(replaced,LEN(replaced)-1))` – Ike Nov 11 '22 at 07:51
  • @Ike I was trying to isolate the issue from another problem. The sample itself is useless because it returns almost the same as the input. The only purpose is to show the issue I encountered in other situations. – David Leal Nov 11 '22 at 13:58
  • 1
    @JosWoolley I tested: `=LET(input, {"a|b\c|d\";"e|d\f|g\"}, BYROW(input, LAMBDA(item, TEXTJOIN("\",,TEXTSPLIT(item,"|","\", TRUE)))))` and it doesn´t work, the same result. If you change the delimiters in `input`, you need to change them in `TEXTJOIN` and in `TEXTSPLIT` too. The workaround that works is the one pointed out by @JvdV in his answer. – David Leal Nov 12 '22 at 00:16

1 Answers1

3

Not sure if this would classify as an answer but thought I'd share my attempt at it.

I don't think the problem here is TEXTSPLIT(). I tried different things. 1st I tried to incorporate FILTERXML() to do the split, with the exact same result. For good measure:

=BYROW({"a,b;c,d;","e,d;f,g;"},LAMBDA(item,TEXTJOIN(";",,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(item,",",";"),";","</s><s>")&"</s></t>","//s"))))

Then I tried to enforce array usage with T(IF(1,TEXTSPLIT("a,b;c,d;",{",",";"},,1))) but Excel would not budge.

The above lead me to believe the problem is in fact BYROW() itself. Even though documentation says the 1st parameter takes an array, the working with other array-functions do seem to be buggy and you could report it as such.

For what it's worth for now; you could use REDUCE() as mentioned in the comments and in the linked answer however I'd preserve that for more intricate stacking of uneven distributed columns/rows. In your case MAP() will work and is simpler than BYROW():

=LET(input, {"a,b;c,d;";"e,d;f,g;"},
  MAP(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(item,",",";", TRUE)))))

And to be honest, this is kind of what MAP() is designed for anyway.

David Leal
  • 6,373
  • 4
  • 29
  • 56
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks, @JvdV, I haven´t tested replacing `BYROW` with `MAP` and it works, so probably you are correct the problem is `BYROW` and not `TEXTSPLIT`. Do you know where a Microsoft Excel bug can be reported? Thanks – David Leal Nov 11 '22 at 13:47
  • @DavidLeal [here](https://support.microsoft.com/en-us/office/how-do-you-report-an-issue-or-bug-a318252f-cc24-4e95-bec9-71bd2dbc379d) you go. – JvdV Nov 11 '22 at 14:42
  • 1
    Thanks, JvdV I tried that on a different occasion, after being on the phone for about 1 hour. They told me that I need to go with the internal process in my company to the person who owns the business account because this is the only one who can interact with the support team, so I cannot post a bug like in other products like Jira, this is not possible with Microsoft, it has to go through the official channels and the email doesn't work. Thanks anyway – David Leal Nov 11 '22 at 15:20
  • I believe the problem with `BYROW` and `TEXTSPLIT` is that according to the documentation, the associated `LAMBDA` can only return a single value – Ron Rosenfeld Dec 06 '22 at 01:08
  • 1
    @RonRosenfeld I found out that it happens even when `TEXTSPLIT` is not used with `BYROW`, for example: `=BYROW(SEQUENCE(2), LAMBDA(i, SUM(INDEX({1,2;3,4},i,))))` produces a wrong result, but using implicit intersection (`@`) it works: `=BYROW(SEQUENCE(2), LAMBDA(i, SUM(INDEX({1,2;3,4},@i,))))`. Check EEM's answer to this question: [How to make lambda function working on excel365?](https://stackoverflow.com/questions/75039133/how-to-make-lambda-function-working-on-excel365), it is weird but it works. A bug or some specification not documented in `BYROW`. – David Leal Mar 24 '23 at 04:36
  • 1
    @DavidLeal, strange but nice find! – JvdV Mar 24 '23 at 12:38
  • It is @RonRosenfeld and the rational that requires to use `@` is not always valid, for example for this case it not required: `=BYROW(SEQUENCE(2), LAMBDA(i, SUM(CHOOSEROWS({1,2;3,4},i))))` it works with and without `@`, i.e. with `INDEX` is required but not with `CHOOSECOL`. – David Leal Mar 24 '23 at 13:04
  • Actually @JvdV I found the explanation about the `BYROW` behavior, `item` is an array. it can be cast to scalar with `@`-operator as follows and it works: `=LET(input, {"a,b;c,d;";"e,d;f,g;"}, BYROW(input, LAMBDA(item, TEXTJOIN(";",,TEXTSPLIT(@item,",",";", TRUE)))))`. [Here](https://techcommunity.microsoft.com/t5/excel/another-issue-with-byrow/m-p/3777828) is the explanation. [BYROW documentation](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb) only says: *row: A row from array*, but that is the reason, not well documented. – David Leal Mar 25 '23 at 18:47