英文:
TEXTSPLIT inside CHOOSECOLS inside another TEXTSPLT isn't working as expected
问题
对于一个示例，字符串 A,B;C
我正在使用这个公式，=TEXTSPLIT(CHOOSECOLS(TEXTSPLIT("A,B;C",","),2),";")
TEXTSPLIT 内部将会给出一个包含两个元素的列数组 A 和 B;C
然后，通过对它进行 CHOOSECOLS(*,2)，将会得到 B;C，因为它是数组中的第二个元素
到目前为止都还好。如果我再次使用 ; 作为分隔符对其进行 TEXTSPLIT，然后我期望得到另一个包含两个元素的列数组 B 和 C。但实际上，我只得到了第一个元素作为结果，即 B。这里出了什么问题？这是一个错误吗？我知道如果我像这样做 TEXTSPLIT("A,B;C",{",",";"})，我会得到我的结果，但我有一种不同的情况。我的实际文本看起来更像 A,B,C;D,E,F，而我需要 D E F 作为数组。如果我做类似 =TEXTSPLIT(CHOOSECOLS(TEXTSPLIT("A,B,C;D,E,F",";"),2),",")，我只得到 D，而不是预期的数组
我还观察到的另一个情况是，如果我将操作拆分成两个部分，例如将 CHOOSECOL 的结果放在一个单元格中，然后在另一个单元格中执行另一个 TEXTSPLIT，我会得到结果。但我不想在另一个单元格中执行它。
英文:
For an example, string A,B;C
I am doing this formula, =TEXTSPLIT(CHOOSECOLS(TEXTSPLIT("A,B;C",","),2),";")
TEXTSPLIT inside will give 2 element column array A and B;C
Then, CHOOSECOLS(*,2) over it will give B;C since it is the 2nd element in the array
It is ok till now. If I TEXTSPLIT over this with ; as a separator, then I expect again a 2-element column array B and C. Instead, I get only the first element as result, that is B. What is going wrong here? Is it a bug? I know if I do like TEXTSPLIT("A,B;C",{",",";"}), I will get my result, but I have a different sort of scenario. My actual text looks more like A,B,C;D,E,F, and I need D E F as array. If I do something like =TEXTSPLIT(CHOOSECOLS(TEXTSPLIT("A,B,C;D,E,F",";"),2),","), I get only D, instead of expected array
Another observation I had is that, if I spilt the operation into 2, say I put the result of CHOOSECOL in a cell, and do another TEXTSPLIT in another cell, I get the result. But I don't want to do it in another cell.
答案1
得分: 1
=TEXTSPLIT(INDEX(TEXTSPLIT("A,B,C;D,E,F",";"),2),",")
英文:
As I mentioned in my comment, simply replace CHOOSECOLS with INDEX:
=TEXTSPLIT(INDEX(TEXTSPLIT("A,B,C;D,E,F",";"),2),",")
答案2
得分: 1
对于这种情况，使用隐式交集运算符 (@) 可以解决您的问题。只需在公式中在CHOOSECOLS之前添加@运算符：
=TEXTSPLIT(@CHOOSECOLS(TEXTSPLIT("A,B;C",","),2),";")
输出：
CHOOSECOLS 返回一个 1x1 数组，而正如其名称所示，TEXTSPLIT 在文本（字符串）级别上操作。@ 运算符将数组转换为字符串。我们本来期望Excel会为我们执行此转换，但实际上没有发生。
您可以通过使用评估公式功能查看Excel执行的步骤来验证上述推理：
CHOOSECOLS 调用后的中间结果：
@ 运算符后的中间结果：
这与@JosWoolley的答案使用INDEX得到的相同中间结果。
英文:
For this case using the Implicit Intersection operator (@) solves your problem. Just add to your formula the @-operator before CHOOSECOLS:
=TEXTSPLIT(@CHOOSECOLS(TEXTSPLIT("A,B;C",","),2),";")
Output:
CHOOSECOLS returns 1x1 array and TEXTSPLIT as its name indicates, it operates at a text (string) level. The @-operator converts the array into a string. We would expect here that Excel would do the cast for us, but it doesn't happen.
You can check the above rationale by looking at the steps Excel carries out via Evaluate Formula functionality:
The intermediate result after CHOOSECOLS call`:
The intermediate result after @-Operator:
It is the same intermediate result as @JosWoolley's answer using INDEX.
答案3
得分: 0
=TEXTSPLIT(TEXTJOIN('', CHOOSECOLS(TEXTSPLIT('A,B;C', ','), 2), ';'))
CONCAT() may also work.
=TEXTSPLIT(CONCAT(CHOOSECOLS(TEXTSPLIT('A,B;C', ','), 2), ';'))
英文:
Try-
=TEXTSPLIT(TEXTJOIN("",,CHOOSECOLS(TEXTSPLIT("A,B;C",","),2)),";")
CONCAT() may also work.
=TEXTSPLIT(CONCAT(CHOOSECOLS(TEXTSPLIT("A,B;C",","),2)),";")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论