☰

从没有模式的字符串中提取日期

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba
从没有模式的字符串中提取日期

我最近开始处理依赖于 Oracle 数据库上发布的描述字符串的 Excel 表。

字符串如下

1438 年 8 月 18 日至 1439 年 8 月 17 日 / 17 年 5 月 15 日至 18 年 5 月 4 日期间停车租金为 1 年

街道租金为 6 个月，从 2014 年 8 月 1 日至 2014 年 2 月 1 日 / 2017 年 4 月 28 日至 2017 年 10 月 21 日

310113-Outlet 1 年租金，自 1438 年 1 月 1 日至 1438 年 12 月 30 日

以及发布描述的人感觉舒服的任何其他形式。

现在我的问题是如何通过 excel 函数或 vba 宏轻松地从这些描述中提取日期。额外的好处是能够即时将伊斯兰历日期转换为公历日期，而不必通过 Web 服务进行转换。

编辑：忘记提到所需的输出（感谢 Mate Juhasz），输出必须是可以在 days360 公式中使用的内容，例如 mm/dd/yyyy。每个条目中通常有两个日期，即合同开始日期和合同结束日期，并且可能会同时发布相同的日期和回历日期，但很少只发布回历日期。请务必检查 dd/mm/yyyy 或 dd/mm/yy，因为这是他们通常的发布格式。

答案1

1. 提取日期

我用RegEx 查找/替换在这种情况下，可以使用插件，它为 Excel 添加正则表达式功能。

您也可以将其用作数组公式：

  • 由于您期望每个单元格最多有 4 个日期，请选择一行中的四个单元格（例如示例中的 B3:E3）
  • 类型公式：
    =RegExFind(A3,"\d+-\d+-\d+")
  • 按 CTRL+Shift+Enter
  • 向下复制公式

这将查找所有以 1/2 位数字的日期/月份和 2/4 位数字的年份输入的日期，各部分之间用单个-

2. 将提取的文本转换为日期

在示例中DATEVALUE，我使用了仅适用于公历的函数，我没有使用伊斯兰历的经验。

在此处输入图片描述

答案2

以下是使用 VBA 而不使用 Regex 的解决方案：

Sub GetDateCandidates()
    Dim i As Long, N As Long, s As String
    Dim K As Long, a, bry

    K = 2
    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To N
        s = Cells(i, 1).Value
        ary = Split(s, " ")
        For Each a In ary
            bry = Split(a, "-")
            If UBound(bry) = 2 Then
                If (TestBry(bry)) Then
                    Cells(i, K).Value = "'" & a
                    K = K + 1
                End If
            End If
        Next a
        K = 2
    Next i
End Sub

Public Function TestBry(b) As Boolean

    TestBry = False

    If Not IsNumeric(b(0)) Then Exit Function
    If Not IsNumeric(b(1)) Then Exit Function
    If Not IsNumeric(b(2)) Then Exit Function
    If Len(b(0)) > 2 Then Exit Function
    If Len(b(1)) > 2 Then Exit Function
    If Len(b(2)) = 2 Or Len(b(2)) = 4 Then TestBry = True
End Function

短语在列中A。每个短语使用空格作为分隔符分成单词。每个单词使用短跑作为分隔符。如果：

  1. 有三件物​​品
  2. 所有三项都是数字
  3. 前两个项目的长度可以是1或者2
  4. 第三项的长度可以是2或者4

该单词存储在短语旁边：

在此处输入图片描述

答案3

在您给出的例子中，它确实遵循一种模式。

模式是：至少一位数字，后跟一个破折号或斜线符号。（x2）并以 2 位或 4 位数字结尾。它们都在同一行。

因此，您可以使用正则表达式返回与此类模式匹配的所有结果。我的正则表达式有点生疏，但模式应该是这样的：

\d{1,2}[\/-]\d{1,2}[\/-]\d{2,4}

例如，这将匹配 DD/MM/YYYY 格式以及 DM-YY 格式及其排列。

如果你想解析从到日期格式，您可以使用以下搜索字符串：

\d{1,2}[\/-]\d{1,2}[\/-]\d{2,4}\s*to\s*\d{1,2}[\/-]\d{1,2}[\/-]\d{2,4}

当然，您需要通过正则表达式引擎运行它并收集输出。

然后你可能想要标准化上述输出，以便它全部匹配日/月/年格式——尽管我不得不说我更喜欢年-月-日考虑到计算机如何索引搜索内容。您也可以使用正则表达式执行此操作，方法是将数字分组并使用搜索和替换重新组织它们。如果您愿意尝试，请告诉我。

最后，您可能希望将标准化输出转换为公历。如果年份值低于 1900 年或类似年份，您可以使用 Excel 公式运行计算。

祝你好运。尽管脚本和正则表达式有很大帮助，但像这样标准化数据可能是一场噩梦。

答案4

您可以随时创建自己的 RegEx 函数来简化：

    Function RegEx(Target As String, RegExpression As String, _
                   Optional ReplaceString As String, Optional xIgnoreCase As Boolean, _
                   Optional xGlobal As Boolean, Optional xMultiLine As Boolean)
    
        Dim regexOne As Object
                
        Set regexOne = New RegExp
        regexOne.Pattern = RegExpression
        If xIgnoreCase Then regexOne.IgnoreCase = xIgnoreCase
        If xGlobal Then regexOne.Global = xGlobal
        If xMultiLine Then regexOne.MultiLine = xMultiLine
        
        If regexOne.Test(Target) Then
            If IsMissing(ReplaceString) Then
                RegEx = regexOne.Execute(Target)
            Else
                RegEx = regexOne.Replace(Target, ReplaceString)
            End If
        End If
            
    End Function

相关内容