时间戳

首页 > 解决方案 > 如何从字符串中提取日期？

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

问题描述

我正在尝试仅通过 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','')!=.]"))

我的预期输出看起来像 -

在此处输入图像描述

标签： excelexcel-formula

解决方案

如果您想纯粹使用 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。

推荐阅读