如何用Google Sheets公式将带标记的单列结构化数据转多列？

步骤1：给数据分组编号 #

在B1单元格输入以下公式，自动为每组数据分配唯一ID（过滤掉开头的&++ BEGIN和结尾的&++ EOF (data)）：

=SCAN(0,A:A,LAMBDA(a,c,IF(LEFT(c,1)="[",a+1,IF(OR(c="&++ BEGIN",c="&++ EOF (data)"),0,a))))

步骤2：提取各字段内容 #

• 提取标题：在C1输入公式，提取[..]内的标题文本：

  =IF(LEFT(A:A,1)="[",REGEXEXTRACT(A:A,"^\[(.*)\]$"),"")
  

• 提取默认字符串：在D1输入公式，移除~标记并提取内容：

  =IF(LEFT(A:A,1)="~",SUBSTITUTE(A:A,"~",""),"")
  

• 提取选项内容：在E1输入公式，移除£££标记并提取内容：

  =IF(LEFT(A:A,3)="£££",SUBSTITUTE(A:A,"£££",""),"")
  

步骤3：按组聚合生成四列结果 #

1. 在F1输入公式，获取所有有效组的ID：

   =UNIQUE(FILTER(B:B,B:B>0))
   

2. 在G2输入公式，提取对应组的标题（下拉填充）：

   =INDEX(FILTER(C:C,B:B=F2),1)
   

3. 在H2输入公式，提取对应组的默认字符串（下拉填充）：

   =INDEX(FILTER(D:D,B:B=F2),1)
   

4. 在I2输入公式，拼接对应组的所有选项（下拉填充）：

   =TEXTJOIN(", ",TRUE,FILTER(E:E,B:B=F2))
   

最终F-I列就是你需要的四列结果（组ID、标题、默认字符串、拼接选项）。

简化版单公式方案 #

如果不想用辅助列，可以直接用嵌套公式一次性生成结果，在空白单元格（比如B1）输入：

=LET(
  data,FILTER(A:A,NOT(OR(A:A="&++ BEGIN",A:A="&++ EOF (data)",A:A=""))),
  groups,SCAN(0,data,LAMBDA(a,c,IF(LEFT(c,1)="[",a+1,a))),
  titles,IF(LEFT(data,1)="[",REGEXEXTRACT(data,"^\[(.*)\]$"),""),
  defaults,IF(LEFT(data,1)="~",SUBSTITUTE(data,"~",""),""),
  options,IF(LEFT(data,3)="£££",SUBSTITUTE(data,"£££",""),""),
  unique_groups,UNIQUE(groups),
  BYROW(unique_groups,LAMBDA(g,{
    g,
    INDEX(FILTER(titles,groups=g),1),
    INDEX(FILTER(defaults,groups=g),1),
    TEXTJOIN(", ",TRUE,FILTER(options,groups=g))
  }))
)

这个公式会直接输出包含组序号的四列结果。

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