![从没有模式的字符串中提取日期](https://linux22.com/image/1547252/%E4%BB%8E%E6%B2%A1%E6%9C%89%E6%A8%A1%E5%BC%8F%E7%9A%84%E5%AD%97%E7%AC%A6%E4%B8%B2%E4%B8%AD%E6%8F%90%E5%8F%96%E6%97%A5%E6%9C%9F.png)
我最近开始处理依赖于 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
- 第三项的长度可以是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