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

标准日期转换

Function 标准日期(ByVal str$) As DateDim 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 = patresult = .Replace(str, "$1/$2/$3")End With标准日期 = Format(result, "yyyy/mm/dd")
End Function

标准日期时间提取

Function 标准日期时间(ByVal str$) As DateDim pat$, res$, result$, t&On Error Resume Nextpat = "(\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 = patDoresult = .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 DoIf Len(str) < 6 Then result = "#Error": Exit Do  '没有日期Loop While Len(str) > 1End With标准日期时间 = Format(result, "yyyy/mm/dd hh:mm:ss")
End Function