如何从字符串中提取日期? [英] How to extract dates from string?

问题描述

我正在尝试仅通过Excel公式从字符串中提取日期.以下是我的数据示例

I am trying to extract dates from string by excel formula only. Below is my data sample

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()公式来实现.我已经尝试了以下公式，也尝试了其他几种方法，但是失败了.

My computer local date format is dd/mm/yyyy. I was trying to accomplish it by FILTERXML() formula. I have tried below formula, also tried few other methods but fails.

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

我的预期输出看起来像-

My expected output looks like-

推荐答案

如果您只想使用xpath，则可以通过以下步骤尝试完全验证模式 dd/mm/yyyy : ₁

If you want to go purely xpath then you could try to fully validate your pattern dd/mm/yyyy in a few steps:₁

=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)='/'] -第三个索引处有一个正斜杠；
  • [substring(.，6，1)='/'] -第6个索引处有一个正斜杠;
  • [string-length(translate(.，'/'，``))= 8] -替换正斜杠时，节点的其余长度为八.
  • [translate(.，'/'，'')* 0 = 0] -替换正斜杠时节点的其余部分为数字.
  • "<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>" - Create a valid XML-construct.
  • //s - Select s-nodes where:
    • [substring(., 3, 1)= '/'] - There is a forward slash at the 3rd index;
    • [substring(., 6, 1)= '/'] - There is a forward slash at the 6th index;
    • [string-length(translate(., '/' , '')) = 8] - The remainder of the node when we replace the forward slashes is of length eight.
    • [translate(., '/' , '')*0=0] - The remainder of the node when we replace the forward slashes is numeric.

    不用说，如果您的字符串不包含其他任何正斜杠，而是日期中的斜杠，则可以显着简化上述 ₁ :

    Needless to say that if your string does not hold any other forward slashes but those in the dates you can simplify the above significantly₁:

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

    请注意，如果"dd/mm/yyyy"被Excel识别为日期，仅使用//s 返回的数组将返回这些日期的数值等效形式.如果您的字符串中不存在其他数值，则可以使用Microsoft365功能 ₁ :

    Notice that if "dd/mm/yyyy" is recognized by Excel as dates, the returned array of simply using //s would return the numeric equivalent of these dates. If no other numeric values exist in your string you could benefit from that using Microsoft365 functionality₁:

    =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}

    _{1: Note that you can remove the nested TEXT() function and numberformat your cells to dd/mm/e too.}

    这篇关于如何从字符串中提取日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！