Google Sheets中从单列可变字段半结构化数据提取表格

问题背景 #

我已将数据粘贴到Google Sheets的A列，希望提取指定信息转换为结构化表格。核心疑问是：当数据为单列半结构化形式（每组独立数据遵循通用顺序，但字段数量可变）时，选择所需数据并整理为表格的最优方法是什么？类似把数据库JSON粘贴成单列、每条记录字段数可变的场景，用QUERY、匹配/索引，还是我自己尝试的正则拆分方法更好？

示例场景是为James Madison安排会议，需要从A列提取信息生成如下目标表格：

半结构化数据源 #

粘贴到A2的数据集，每组人员信息遵循固定顺序，但电话号码等字段存在可变内容（部分人员有工作/家庭电话），且重复标识PNPending arrival可用于拆分每组数据：

现有方案及输出 #

我使用的公式如下：

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(JOIN("😆",PasteApppointment!A2:A31),"James Madison😆",""),"H..\(\d+\).\d+.\d{4}😆|W..\(\d+\).\d+.\d{4}😆",""),"M..\((\d+)\).(\d+).(\d{4})","$1$2$3"),"PNPending arrival😆",FALSE,FALSE)), "😆",FALSE,FALSE))

核心逻辑：先移除无关内容（James Madison及W/H开头的电话），再用PNPending arrival拆分每组数据，同时将M开头的手机号格式化为纯数字。输出结果如下：

方案对比与最优选择 #

1. 正则拆分方案 #

优点：

• 一次性完成清洗、拆分、格式转换，适合字段顺序固定的场景
• 用特殊分隔符（😆）避免与数据内容冲突，逻辑清晰
  缺点：
• 正则表达式复杂度高，后期维护/修改成本大，比如字段顺序变化或新增字段时需要大幅调整
• 最后一行出现多余的PNPending arrival，需要额外处理

2. QUERY函数方案 #

QUERY适合结构化数据的筛选，但对于半结构化单列数据，需要先将数据分组，再用QUERY提取，步骤繁琐，不如正则拆分直接。

3. INDEX/MATCH方案 #

适合按固定位置提取字段，但因为存在可变字段（如多电话），需要先定位每组数据的起始/结束位置，再逐个提取，公式会非常冗长，扩展性差。

最优结论 #

在字段顺序固定、有明确分组标识的场景下，你的正则拆分方案是效率最高的。可以优化一下公式解决最后一行的多余内容问题：

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(REGEXREPLACE(SUBSTITUTE(JOIN("😆",PasteApppointment!A2:A31),"James Madison😆",""),"H..\(\d+\).\d+.\d{4}😆|W..\(\d+\).\d+.\d{4}😆",""),"M..\((\d+)\).(\d+).(\d{4})","$1$2$3"),"😆PNPending arrival",FALSE,FALSE)), "😆",FALSE,FALSE))

调整点：将拆分标识从PNPending arrival😆改为😆PNPending arrival，避免最后一组末尾的分隔符残留。

另外，如果未来字段顺序可能变化，建议先通过MATCH定位分组标识的位置，再用INDEX提取每组内的对应字段，虽然公式长，但可读性和扩展性更好。

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