excel - 如何从字符串中提取日期？

Question

我正在尝试仅通过 excel 公式从字符串中提取日期。下面是我的数据样本

Only contains one date 01/05/2021 to this example
Project start date is 01/01/2021 and end date is 31/01/2021
There may multiple date like 1st 01/01/2021 2nd 01/06/2021 and 3rd 31/12/2021

我的电脑本地日期格式是 dd/mm/yyyy .我试图通过 FILTERXML() 来完成它公式。我尝试了下面的公式，也尝试了其他一些方法但失败了。

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[translate(.,'dd/mm/yyyy','')!=.]"))

我的预期输出看起来像 -

Answer 1

如果您想纯粹使用 xpath，那么您可以尝试完全验证您的模式 dd/mm/yyyy只需几个步骤:1

=TRANSPOSE(TEXT(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[substring(., 3, 1)= '/'][substring(., 6, 1)= '/'][string-length(translate(., '/' , '')) = 8][translate(., '/' , '')*0=0]"),"dd/mm/e"))

"<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>" - 创建一个有效的 XML 结构。

//s - 选择 s 节点，其中:

[substring(., 3, 1)= '/'] - 在第 3 个索引处有一个正斜杠；

[substring(., 6, 1)= '/'] - 第 6 个索引处有一个正斜杠；

[string-length(translate(., '/' , '')) = 8] - 当我们替换正斜杠时，节点的剩余部分长度为 8。

[translate(., '/' , '')*0=0] - 当我们替换正斜杠时，节点的其余部分是数字。

不用说，如果您的字符串不包含任何其他正斜杠，但日期中的那些您可以显着简化上述内容1:

=TRANSPOSE(TEXT(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[contains(., '/')]"),"dd/mm/e"))

请注意，如果 Excel 将 "dd/mm/yyyy"识别为日期，则简单使用 //s 的返回数组将返回这些日期的数字等价物。如果您的字符串中不存在其他数值，您可以使用 Microsoft365 功能1 从中受益:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"),TRANSPOSE(TEXT(FILTER(X,ISNUMBER(X)),"dd/mm/e")))

1: 备注 您可以删除嵌套的 TEXT()函数和数字将您的单元格格式化为 dd/mm/e也。