Excel·VBA日期时间转换提取正则表达式函数

标准日期转换

Function 标准日期(ByVal str$) As Date
    Dim pat$, result$
    arr = Array("(\d{4}).*?(\d{1,2}).*?(\d{1,2})", "(\d{4}).*?(\d{1}).*?(\d{1,2})")
    If Len(str) < 8 Then pat = arr(1) Else pat = arr(0)
    With CreateObject("vbscript.regexp")  '正则表达式
        .Global = True
        .Pattern = pat
        result = .Replace(str, "$1/$2/$3")
    End With
    标准日期 = Format(result, "yyyy/mm/dd")
End Function
1
2
3
4
5
6
7
8
9
10
11

标准日期时间提取

Function 标准日期时间(ByVal str$) As Date
    Dim pat$, res$, result$, t&
    On Error Resume Next
    pat = "(\d{4}).*?(\d{1,2}).*?(\d{1,2}).*?(\d{1,2}).*?(\d{1,2}).*?(\d{1,2}).*"
    res = "$1/$2/$3 $4:$5:$6"
    With CreateObject("vbscript.regexp")  '正则表达式
        .Global = True
        .Pattern = pat
        Do
            result = .Replace(str, res): t = CLng(Mid(result, 1, 4))
            If CDate(result) = "0:00:00" Or t < 1900 Then str = Mid(str, 2) Else Exit Do
            If Len(str) < 6 Then result = "#Error": Exit Do  '没有日期
        Loop While Len(str) > 1
    End With
    标准日期时间 = Format(result, "yyyy/mm/dd hh:mm:ss")
End Function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16