如何基于特殊字符将Excel单列数据拆分至多列？

原数据格式 #

Special Char
row1
row2
row3
row4
row5
Special Char
row1
row2
row3
row4
Special Char
row1
row2
row3
row4
Special Char
row1
row2

期望转换后格式 #

Special Char   Special Char   Special Char   Special Char
row1           row1           row1           row1
row2           row2           row2           row2
row3           row3           row3           
row4           row4           row4           
row5       

                               

这里提供两种实用方法，按需选择：

方法一：Power Query（推荐，操作高效） #

这是Excel自带的批量处理工具，步骤清晰：

1. 选中你的单列数据，点击顶部数据选项卡，选择从表格/区域（Excel 2016及以后版本直接用，旧版本需先安装Power Query加载项）
2. 在Power Query编辑器中，添加索引列：点击添加列 → 索引列 → 从0开始
3. 新建「分组标记」列：点击添加列 → 自定义列，输入公式 =if [Column1] = "Special Char" then 1 else 0，确认生成
4. 新建「分组ID」列：再添加一个自定义列，输入公式 =List.Accumulate(List.Range(Source[分组标记],0,[索引]+1),0,(state,current)=>state+current)，确认生成
5. 删除「索引」和「分组标记」列，仅保留「Column1」和「分组ID」
6. 点击转换选项卡 → 透视列，在弹窗中设置：
   • 值列选「Column1」
   • 列名选「分组ID」
   • 高级选项选择不要聚合
7. 最后点击关闭并上载，数据将自动转换为目标多列格式

方法二：公式法（适合小规模数据手动处理） #

假设原数据在A列，从A1开始：

1. 先统计分组数量：在空白单元格输入 =COUNTIF(A:A,"Special Char")，得到分组数（此处为4）
2. 第一列（如B1）提取第一个分组标题：输入 =INDEX(A:A,SMALL(IF(A:A="Special Char",ROW(A:A)),1))，按Ctrl+Shift+Enter完成数组公式输入
3. B2提取第一个分组的第一行内容：输入 =IFERROR(INDEX(A:A,SMALL(IF((ROW(A:A)>SMALL(IF(A:A="Special Char",ROW(A:A)),1))*(ROW(A:A)<SMALL(IF(A:A="Special Char",ROW(A:A)),2)),ROW(A:A)),ROW()-1)),"")，按Ctrl+Shift+Enter后下拉到出现空值
4. 第二列（C1）提取第二个分组标题：输入 =INDEX(A:A,SMALL(IF(A:A="Special Char",ROW(A:A)),2))，数组回车确认
5. C2提取第二个分组的第一行内容：输入 =IFERROR(INDEX(A:A,SMALL(IF((ROW(A:A)>SMALL(IF(A:A="Special Char",ROW(A:A)),2))*(ROW(A:A)<SMALL(IF(A:A="Special Char",ROW(A:A)),3)),ROW(A:A)),ROW()-1)),"")，数组回车后下拉
6. 后续列以此类推，替换公式中的分组序号即可

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