Excel如何提取单元格文本字符串中的多个日期并比对一致性

需求说明 #

• 数据规则：单个单元格内包含1个及以上主体名称，每个名称后括号内附带YYYY-MM-DD格式的日期，需校验同一单元格内所有日期是否完全相同
• 单元格内容示例：university XXX (2016-10-21) company YYY (2016-10-22)
• 现有进展：已通过公式=MID(A1,SEARCH("(",A1,1)+1,10)实现第一个日期的提取，需要提取后续位置日期完成全量比对

实现方法 #

高版本Excel方案（支持Excel 365/2021及以上版本） #

无需逐个提取日期，可直接通过单公式返回校验结果，效率更高：

=LET(
    cell_content, A1,
    split_res, TEXTSPLIT(cell_content, "(", ")"),
    date_arr, FILTER(split_res, LEN(split_res)=10, ""),
    AND(date_arr=INDEX(date_arr,1))
)

公式返回TRUE代表单元格内所有日期完全一致，返回FALSE代表存在日期不一致的情况。

如果需要单独提取第N个日期，可使用以下公式，将公式中N替换为目标日期的序号即可（例如提取第二个日期就替换为2）：

=LET(
    cell_content, A1,
    split_res, TEXTSPLIT(cell_content, "(", ")"),
    date_arr, FILTER(split_res, LEN(split_res)=10, ""),
    IFERROR(INDEX(date_arr,N), "无对应日期")
)

旧版Excel兼容方案（适用于无动态数组功能的版本） #

提取第N个日期的通用公式如下，同样将N替换为目标日期序号即可：

=IFERROR(MID(A1,SEARCH("(",A1,IF(N=1,1,FIND("@",SUBSTITUTE(A1,"(", "@",N-1))))+1,10),"无对应日期")

公式逻辑：通过替换字符定位第N个左括号的位置，再截取括号后10位长度的日期字符串，和原有提取第一个日期的公式逻辑完全兼容。

如果需要直接完成全单元格日期一致性校验，可使用以下通用公式，无需提前统计单元格内的日期数量：

=SUMPRODUCT(--(MID(A1,SEARCH("(",A1,IF(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"(","")))))=1,1,FIND("@",SUBSTITUTE(A1,"(","@",ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"(","")))))-1))))+1,10)<>MID(A1,SEARCH("(",A1,1)+1,10)))=0

公式返回TRUE代表所有日期一致，返回FALSE代表存在不一致日期。

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