VBA Regular Expression to Match Date
Solution 1
It looks as if your RegEx will only find match if the whole string you pass to it is a date.
Try removing ^
and $
Here's your example reworked using a RegEx that will find dates in the mm/dd/yyyy and mm-dd-yyyy formats -
Private Sub TestDate()
MsgBox RegExDate("cancel on 12/21/2010 ")
End Sub
Private Function RegExDate(s As String) As String
Dim re, match
Set re = CreateObject("vbscript.regexp")
re.Pattern = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}"
re.Global = True
For Each match In re.Execute(s)
MsgBox match.Value
RegExDate = match.Value
Exit For
Next
Set re = Nothing
End Function
Solution 2
Why not use RegEx to get the portion of the string that appears to be the date and use the IsDate Function to validate it?
Function FormatOutput(s)
Dim re, match
Set re = CreateObject("vbscript.regexp")
re.Pattern = "[\d]+[\/-][\d]+[\/-][\d]+"
re.Global = True
For Each match In re.Execute(s)
if IsDate(match.value) then
FormatOutput = CDate(match.value)
Exit For
end if
Next
Set re = Nothing
End Function
The RegEx could be cleared up a bit, but it works for your current example.
HK1
Program Contractor and Manager MS Access, VBA, ADO, SQL Server, MySQL, ASP Classic, ADO.NET, C#, VB.NET, WPF, PHP, Javascript, AngularJS, ASP.NET, ASP.NET MVC, ASP.NET Web API, HTML, CSS, JSON, Android/JAVA, Arduino/C
Updated on August 16, 2020Comments
-
HK1 almost 4 years
I'm new to Regular Expressions and am having difficulty getting patterns that I find online to work in VBScript/VBA. This one is supposed to return a date found in a string but it fails to find any dates. What does VBScript/VBA do different than other RegEx engines that makes this fail to return a match?
Edit1
I removed the ^ and the $ from my pattern. The problem persists.Private Sub TestDate() MsgBox RegExDate("cancel on 12/21/2010 ") End Sub Private Function RegExDate(s As String) As String Dim re, match Set re = CreateObject("vbscript.regexp") re.Pattern = "(((0[1-9]|[12]\d|3[01])\/(0[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\/(0[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\/02\/((19|[2-9]\d)\d{2}))|(29\/02\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))" re.Global = True For Each match In re.Execute(s) MsgBox match.value RegExDate = match.value Exit For Next Set re = Nothing End Function
-
HK1 over 12 yearsGreat suggestion but that doesn't fix the problem.
-
ipr101 over 12 yearsI've updated the answer with a new RegEx in your code sample, not sure if it's too specific though?
-
HK1 over 12 yearsWill this find dates that are separated by dashes? But yeah, testing the string returned is certainly OK in this case.
-
HK1 over 12 yearsAny explanation on the changes that were needed? Also any ideas on how to make this find (mm/dd/yyyy) or (mm-dd-yyyy)?
-
Rob Haupt over 12 yearsUpdated the Pattern. it will match on / or - for date separators.
-
ipr101 over 12 yearsIt will already find mm-dd-yyyy, sorry should have put that in the last comment
-
pastacool over 12 yearsbe aware, that will also match invalid dates eg. Feb 31st
-
keun about 10 yearsGreat answer! Always having trouble with regular expressions :-s