如何用Excel内置函数对动态范围的竖线分隔文本进行拆分？

我从ERP系统导出了一份长数据集，数据以竖线|作为分隔符，需要拆分到单独列中。可以使用FILTERXML()或TEXTSPLIT()函数，但希望实现动态拆分——新增数据行时能自动完成拆分。

以下是样本数据（每行对应单个单元格）：

HANG TAG (FG00028 NEXT||||(69 X 18)mm|||U LABEL|||||1631/2022|||||||||)             
BOX END LABEL (FG00781 NEXT||||(114 X 68)mm|||NEXT-BK|||||1804/22|||||||||)             
HANGER STICKER (FG00840 NEXT||||(40 X 40)mm|||WWL251|||||1616/22|||||||||)               
HANGER STICKER (FG00840 NEXT||||(34 X 17) mm|||WWL251|||||1621/2022|||||||||)               
CARE LABEL (FG00722 NEXT|CO-069593[QTY:2248]PER:0.35%|||(130X 25)mm|||NEXT-NF|||||1573/22|||||||||)             
CARE LABEL (FG00722 NEXT||||(130X 25)mm|||SWS-COM|||||1578/2022|||||||||)               
CASCADE CARD (FG00780 GEORGE|1078230-31-28-29|||(601 X 276.5) mm|||MUPC2||LIZ|||1639/22|||||||||)               
CARE LABEL (FG00722 NEXT||||(130X 25)mm|||SWS-SIM|||||1573/22|||||||||)             
CARE LABEL (FG00722 GEORGE|PO-1077981|||(20X70)mm|||CLGW|||||1734/2022|||||||||)                 
BOX END LABEL (FG00781 NEXT||||(65X 105)mm|||BK|||||1177/22|||||||||)               
WOVEN MAIN LABEL (FG00806 GEORGE|PO-1084217 ERPNO-22S23P111037/1|||10X77MM|||GCBMF|||||1752/2022|||||||||)             
OVER RIDER (FG00826 Sainsbury|PP sample for developing|||31X95MM|||TU-DENOV-L2|||||365/22|||||||||)             
DISCLAIMER TAG (FG00829 SAINSBURY|2523229/141048665||||||TU-DISCSW24|||||1571/22|||||||||)               
HANGER STICKER (FG00840 GEORGE|1071004-1070769-70-1070764-65-66-67-1071006-1070776|||37X24MM|||MLH|||||1462/2022|||||||||)               
DISCLAIMER TAG (FG00829 SAINSBURY|2523238/1410980784||||||TU-DISCSW24|||||1572/22|||||||||)

我曾尝试结合TEXTSPLIT()和TEXTJOIN()实现动态拆分，公式如下：

=TEXTSPLIT(TEXTJOIN("#",TRUE,A1:A15),"|","#")

该公式能得到预期结果，但TEXTJOIN()存在字符限制，无法用于长数据集。请问如何使用Excel内置函数对动态范围的文本进行拆分？

方案1：Excel 365/2021 动态数组方案（推荐） #

利用BYROW遍历动态范围的每一行，结合TEXTSPLIT直接拆分，无需TEXTJOIN，彻底避免字符限制问题。公式如下：

=BYROW(A1:INDEX(A:A,COUNTA(A:A)),LAMBDA(x,TEXTSPLIT(x,"|")))

• 动态范围说明：A1:INDEX(A:A,COUNTA(A:A))会自动匹配A列所有非空行，新增数据行后，公式会自动更新范围并扩展结果。
• 效果：返回二维动态数组，每行对应原数据行的拆分结果，完整保留原数据中连续|对应的空列。

如果A列存在需要保留的空白行，可将动态范围替换为：

A1:INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A)))

方案2：兼容旧版本的FILTERXML方案 #

若使用不支持动态数组的Excel版本，可通过FILTERXML结合字符串转换实现动态拆分：

=WRAPROWS(FILTERXML("<t><s>"&SUBSTITUTE(A1:INDEX(A:A,COUNTA(A:A)),"|","</s><s>")&"</s></t>","//s"),MAX(LEN(A1:INDEX(A:A,COUNTA(A:A)))-LEN(SUBSTITUTE(A1:INDEX(A:A,COUNTA(A:A)),"|",""))+1))

• 原理：先将每行数据用SUBSTITUTE转换成XML节点格式，再用FILTERXML提取所有拆分项，最后通过WRAPROWS按最大列数重组为二维数组。
• 注意：旧版本Excel需按数组公式输入（按Ctrl+Shift+Enter），Excel 365可直接使用动态数组特性。

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