I am trying to use regular Expressions to extract the dates from a string using VBA in Excel.
我试图使用常规表达式使用Excel中的VBA从字符串中提取日期。
The string is:
字符串是:
Previous Month: 9/1/2015 - 9/30/2015
Or it can be :
或者它可以是:
Custom: 9/1/2015 - 9/30/2015
Do you have any idea how can I achieve that? I have never used Regular Expressions before.
你知道我怎么能实现这个目标吗?我之前从未使用过正则表达式。
2 个解决方案
#1
2
RegEx is a poor choice for dates. You could look for :
and examine the remaining tokens:
RegEx是日期的糟糕选择。你可以寻找:并检查剩余的令牌:
Sub Foo()
Dim result() As Variant
result = GetDates("Previous Month: 9/1/2015 - 9/30/2015")
If UBound(result) Then
Debug.Print result(0)
Debug.Print result(1)
End If
End Sub
Function GetDates(str As String) As Variant()
Dim tokens() As String
tokens = Split(Mid$(str, InStr(str & ": ", ":")), " ")
If (UBound(tokens) = 3) Then
If IsDate(tokens(1)) And IsDate(tokens(3)) Then
GetDates = Array(CDate(tokens(1)), CDate(tokens(3)))
Exit Function
End If
End If
ReDim GetDates(0)
End Function
#2
1
Try this:
([1-9]|1[012])[/]([1-9]|[1-2][0-9]|3[01])[/](19|20)[0-9]{2}
#1
2
RegEx is a poor choice for dates. You could look for :
and examine the remaining tokens:
RegEx是日期的糟糕选择。你可以寻找:并检查剩余的令牌:
Sub Foo()
Dim result() As Variant
result = GetDates("Previous Month: 9/1/2015 - 9/30/2015")
If UBound(result) Then
Debug.Print result(0)
Debug.Print result(1)
End If
End Sub
Function GetDates(str As String) As Variant()
Dim tokens() As String
tokens = Split(Mid$(str, InStr(str & ": ", ":")), " ")
If (UBound(tokens) = 3) Then
If IsDate(tokens(1)) And IsDate(tokens(3)) Then
GetDates = Array(CDate(tokens(1)), CDate(tokens(3)))
Exit Function
End If
End If
ReDim GetDates(0)
End Function
#2
1
Try this:
([1-9]|1[012])[/]([1-9]|[1-2][0-9]|3[01])[/](19|20)[0-9]{2}