如何使用FILTERXML将特定格式字符串解析为二维数组表格

方法1：纯FILTERXML实现（兼容性更好） #

你可以通过调整XML构造逻辑实现需求，无需额外拆分数组步骤：

=LET(
    case, A5,
    // 一次性替换逗号、x为XML节点分隔符
    full_xml, "<t><s>"&SUBSTITUTE(SUBSTITUTE(case, ",", "</s><s>"), "x", "</s><s>")&"</s></t>",
    // 提取所有节点内容后按2列重排
    all_nodes, FILTERXML(full_xml, "//s"),
    WRAPCOLS(all_nodes, 2)
)

如果你的Excel版本不支持WRAPCOLS函数，可以用以下兼容写法：

=LET(
    case, A5,
    full_xml, "<t><s>"&SUBSTITUTE(SUBSTITUTE(case, ",", "</s><s>"), "x", "</s><s>")&"</s></t>",
    all_nodes, FILTERXML(full_xml, "//s"),
    row_cnt, ROWS(all_nodes)/2,
    CHOOSE({1,2},
        INDEX(all_nodes, SEQUENCE(row_cnt, 1, 1, 2)),
        INDEX(all_nodes, SEQUENCE(row_cnt, 1, 2, 2))
    )
)

方法2：TEXTSPLIT实现（写法更简洁，仅支持Excel 365） #

如果使用最新版Excel，用TEXTSPLIT处理逻辑更直观：

=LET(
    case, A5,
    // 拆分逗号分隔的所有条目
    items, TEXTSPLIT(case, ","),
    // 过滤包含x的有效条目
    valid_items, FILTER(items, ISNUMBER(SEARCH("x", items))),
    // 先拼接再拆分直接得到两列数组
    TEXTSPLIT(TEXTJOIN("|",, valid_items), "x", "|")
)

原公式问题说明 #

你之前的写法中，第二次SUBSTITUTE传入的是第一次拆分得到的垂直数组，拼接XML时没有保留分组关系，最终返回一维长数组，因此只能展示第一列内容。

内容的提问来源于stack exchange，提问作者mark fitzpatrick