Google Sheets字符串提取日期、姓名及活动公式修复需求

问题场景 #

需要从Google Sheets的字符串中批量提取日期、姓名与活动信息，但现有ARRAYFORMULA仅能适配第一行数据，第二行因时间与日期的顺序颠倒导致公式失效。

测试数据 #

假设A列原始数据为：

• A1: 2024-05-20 14:30 张三 参加团队会议
• A2: 15:45 2024-05-21 李四 提交项目报告

现有失效公式 #

=ARRAYFORMULA(IF(A:A="",,{SPLIT(LEFT(A:A,FIND(" ",A:A,FIND(" ",A:A)+1))," "),MID(A:A,FIND(" ",A:A,FIND(" ",A:A)+1)+1,FIND(" ",A:A,FIND(" ",A:A,FIND(" ",A:A)+1)+1)-FIND(" ",A:A,FIND(" ",A:A)+1)-1),RIGHT(A:A,LEN(A:A)-FIND(" ",A:A,FIND(" ",A:A,FIND(" ",A:A)+1)+1))}))

修复后的公式 #

=ARRAYFORMULA(IF(A:A="",,LET(
  raw,A:A,
  split_all,SPLIT(raw," "),
  date_col,BYROW(split_all,LAMBDA(row,XLOOKUP(TRUE,ISDATEVALUE(row),row,""))),
  time_col,BYROW(split_all,LAMBDA(row,XLOOKUP(TRUE,ISNUMBER(TIMEVALUE(row)),row,""))),
  name_col,BYROW(split_all,LAMBDA(row,INDEX(row,XMATCH(FALSE,ISDATEVALUE(row)+ISNUMBER(TIMEVALUE(row)),0,1)))),
  activity_col,BYROW(split_all,LAMBDA(row,TEXTJOIN(" ",TRUE,FILTER(row,NOT(ISDATEVALUE(row)+ISNUMBER(TIMEVALUE(row)))*(row<>INDEX(row,XMATCH(FALSE,ISDATEVALUE(row)+ISNUMBER(TIMEVALUE(row)),0,1)))))),
  {date_col&" "&time_col,name_col,activity_col}
)))

公式逻辑说明 #

1. 拆分字符串：用SPLIT(raw," ")将每行数据按空格拆分成多列数组，统一处理格式差异。
2. 提取日期/时间：通过BYROW遍历每行拆分结果，用XLOOKUP分别匹配符合日期、时间格式的元素，无需依赖固定顺序。
3. 提取姓名：定位第一个既不是日期也不是时间的元素，即为姓名。
4. 提取活动信息：过滤掉日期、时间、姓名后，用TEXTJOIN将剩余内容合并为完整的活动描述。
5. 组合结果：将日期+时间、姓名、活动信息整合为三列批量输出。

该公式不受日期与时间的顺序影响，可适配多种格式的原始字符串，同时支持多行数据批量处理。

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