Google Sheets正则表达式需求：提取日期金额商品名并过滤无关内容

需求目标 #

• 仅提取购买日期、金额及所购商品名称
• 忽略所有空行
• 忽略参考编号（Reference #）及"SHIPPING AND TAX"字符串
• 对每组交易重复上述操作

现有问题 #

使用以下公式未达到预期效果：

=index(if(len(C26:C33),REGEXREPLACE(C26:C33,"(?Ums)(\d{2}/\d{2}) .* (\$\d{1,}\.\d{1,2}).(?:^\s+\d+$)(.*)(?:\s+SHIPPING AND TAX)","$1,$2,$3"),))

具体问题：

• 未正确忽略日期与金额间的冗余内容、空行及"SHIPPING AND TAX"字符串
• 出现#VALUE!错误，无法处理数字类型的参考编号（提示REGEXREPLACE参数1需文本值）

修正方案 #

核心公式（单区域处理） #

=ARRAYFORMULA(
  IF(
    LEN(C26:C33)=0,,
    IFERROR(
      REGEXREPLACE(
        TO_TEXT(C26:C33),
        "^(?!.*(Reference #|SHIPPING AND TAX))(\d{2}/\d{2}).*?(\$\d+\.\d{2}).*?([^\n]+)$",
        "$1,$2,$3"
      ),
      ""
    )
  )
)

公式说明 #

1. TO_TEXT(C26:C33)：将所有单元格内容转为文本，彻底解决数字类型数据导致的正则报错问题
2. ^(?!.*(Reference #|SHIPPING AND TAX))：负向前瞻规则，直接排除包含参考编号或"SHIPPING AND TAX"的行
3. (\d{2}/\d{2})：精准提取MM/DD格式的购买日期
4. .*?(\$\d+\.\d{2})：非贪婪匹配，准确抓取$开头、保留两位小数的交易金额
5. .*?([^\n]+)$：非贪婪匹配至行尾，提取完整商品名称
6. IF(LEN(C26:C33)=0,,)：自动过滤空行
7. IFERROR(..., "")：对不符合规则的行返回空值，避免错误提示

批量交易组处理 #

若交易数据按组排列（每组包含日期、金额、商品、参考号、运费税等），可使用以下公式批量筛选有效交易行：

=QUERY(
  ARRAYFORMULA(
    IFERROR(
      REGEXREPLACE(
        TO_TEXT(C26:C),
        "^(\d{2}/\d{2}).*?(\$\d+\.\d{2}).*?([^\n]+)$",
        "$1,$2,$3"
      ),
      ""
    )
  ),
  "WHERE Col1 <> '' AND NOT Col1 CONTAINS 'Reference #' AND NOT Col1 CONTAINS 'SHIPPING AND TAX'",
  0
)

该公式会自动遍历整个列，筛选出所有符合格式的交易记录，同时忽略空行、参考编号行和运费税行。

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