用正则方法分离日期的几种方法小结。

ning84 · 发表于 2022-11-15 21:44

Sub ll1()
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As match '成功匹配的对象
Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Set oRegExp = New RegExp
Dim RegArr(5)
RegArr(0) = "([\d]{4})/(0[1-9]|[11-12])/(0[1-9]|[11-12])"
RegArr(1) = "([\d]{2,4})年([\d]{1,2})月([\d]{1,2})日"
RegArr(2) = "([\d]{4})-(0[1-9]|[11-12])-(0[1-9]|[11-12])"
RegArr(3) = "([\d]{4}).(0[1-9]|[11-12]).(0[1-9]|[11-12])"
'REGARR = "([\d]{4})/(0[1-9]|[11-12])/(0[1-9]|[11-12])([\d]{4})年(0[1-9]|[11-12])月(0[1-9]|[11-12])日"
Dim Arr(5)
Arr(0) = "with Regular separate 2022/1/15-Remember"
Arr(1) = "年月日正则分离2022年5月9日/See Me"
Arr(2) = "with Regular separate 2022-1-15-68"
Arr(3) = "with Regular separate 2022.1.15"
Arr(4) = "with Regular separate 11.15"
Arr(5) = "with Regular separate 15/5/2022 Return"
For ii = 0 To 3
oRegExp.Pattern = RegArr(ii)
Set oMatColl = oRegExp.Execute(Arr(ii))
If oMatColl.Count > 0 Then
For jj = 0 To oMatColl.Count - 1
Set oMatch = oMatColl.Item(jj)
Debug.Print Arr(ii), "-----------", oMatch.Value
Next jj
End If
Next ii
End Sub

复制代码

运行结果

with Regular separate 2022/1/15-Remember  ----------- 2022/1/1
年月日正则分离2022年5月9日/See Me          ----------- 2022年5月9日
with Regular separate 2022-1-15-68       ----------- 2022-1-1
with Regular separate 2022.1.15          ----------- 2022.1.1

ning84 · 发表于 2022-11-18 16:19

Sub ll1()
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As match '成功匹配的对象
Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Set oRegExp = New RegExp
Dim RegArr(5), RegStr
RegArr(0) = "([\d]{4})/(0[1-9]|[11-12])/(0[1-9]|[11-12])"
RegArr(1) = "([\d]{2,4})年([\d]{1,2})月([\d]{1,2})日"
RegArr(2) = "([\d]{4})-(0[1-9]|[11-12])-(0[1-9]|[11-12])"
RegArr(3) = "([\d]{4}).(0[1-9]|[11-12]).(0[1-9]|[11-12])"
'REGARR = "([\d]{4})/(0[1-9]|[11-12])/(0[1-9]|[11-12])([\d]{4})年(0[1-9]|[11-12])月(0[1-9]|[11-12])日"
RegStr = "\d{4}(?:年|/|-|.)\d{1,2}月\d{1,2}(?:日|号)"
RegStr = "\d{4}(?:年|/|-|.)\d{1,2}月\d{1,2}(?:日)"
RegStr = "\d{4}(?:年)\d{1,2}(?:月)\d{1,2}(?:日)"
RegStr = "\d{2,4}(?:年|-|/|.)\d{1,2}(?:月|-|/|.)\d{1,2}(日|/||)"
RegStr = "\d{2,4}(年|-|/|.)\d{1,2}(月|-|/|.)\d{1,2}(日|/||)"
RegStr = "\d{2,4}(年|-|/|.)\d{1,2}(月|-|/|.)\d{1,4}(日|/||)"
Dim Arr(5)
Arr(0) = "with Regular separate 2022/1/15-Remember"
Arr(1) = "年月日正则分离2022年5月9日/See Me"
Arr(2) = "with Regular separate 2022-1-15-68"
Arr(3) = "with Regular separate 2022.1.15"
Arr(5) = "with Regular separate 11.15"
Arr(4) = "with Regular separate 15/5/2022 Return"
For ii = 0 To 5
oRegExp.Pattern = RegArr(ii)
oRegExp.Pattern = RegStr
''
Set oMatColl = oRegExp.Execute(Arr(ii))
''
If oMatColl.Count > 0 Then
For jj = 0 To oMatColl.Count - 1
Set oMatch = oMatColl.Item(jj)
Debug.Print Arr(ii), ii, "-----------", oMatch.Value
Next jj
End If
Next ii
End Sub

复制代码

ning84 · 发表于 2022-12-16 12:36

IsDate(Str)

0          2022/1/15    True       String       2022/1/15
1          2022年05月09日 True       String       2022/5/9
2          2022-1-15    True       String       2022/1/15
3          2022.1.15    False       String
5          15/5/2022    True       String       2022/5/15

Sub ll1()
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As Match '成功匹配的对象
Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Set oRegExp = New RegExp
Dim RegArr(5), RegStr
RegArr(0) = "([\d]{4})/(0[1-9]|[11-12])/(0[1-9]|[11-12])"
RegArr(1) = "([\d]{2,4})年([\d]{1,2})月([\d]{1,2})日"
RegArr(2) = "([\d]{4})-(0[1-9]|[11-12])-(0[1-9]|[11-12])"
RegArr(3) = "([\d]{4}).(0[1-9]|[11-12]).(0[1-9]|[11-12])"
'REGARR = "([\d]{4})/(0[1-9]|[11-12])/(0[1-9]|[11-12])([\d]{4})年(0[1-9]|[11-12])月(0[1-9]|[11-12])日"
RegStr = "\d{4}(?:年|/|-|.)\d{1,2}月\d{1,2}(?:日|号)"
RegStr = "\d{4}(?:年|/|-|.)\d{1,2}月\d{1,2}(?:日)"
RegStr = "\d{4}(?:年)\d{1,2}(?:月)\d{1,2}(?:日)"
RegStr = "\d{2,4}(?:年|-|/|.)\d{1,2}(?:月|-|/|.)\d{1,2}(日|/||)"
RegStr = "\d{2,4}(年|-|/|.)\d{1,2}(月|-|/|.)\d{1,2}(日|/||)"
RegStr = "\d{2,4}(年|-|/|.)\d{1,2}(月|-|/|.)\d{1,4}(日|/||)"
Dim oDate As Date, Str
oDate = "2022年05月09日"
''
Dim Arr(5)
Arr(0) = "with Regular separate 2022/1/15-Remember"
Arr(1) = "年月日正则分离2022年05月09日/See Me"
Arr(2) = "with Regular separate 2022-1-15-68"
Arr(3) = "with Regular separate 2022.1.15"
Arr(5) = "with Regular separate 11.15"
Arr(4) = "with Regular separate 15/5/2022 Return"
For ii = 0 To 5
oRegExp.Pattern = RegArr(ii)
oRegExp.Pattern = RegStr
''
Set oMatColl = oRegExp.Execute(Arr(ii))
''
If oMatColl.Count > 0 Then
For jj = 0 To oMatColl.Count - 1
Set oMatch = oMatColl.Item(jj)
Str = oMatch.Value
'Debug.Print Arr(ii),
Debug.Print ii, oMatch.Value,
Debug.Print IsDate(Str), TypeName(Str),
If IsDate(Str) Then
oDate = oMatch.Value
Debug.Print oDate
Else
Debug.Print
End If
Next jj
End If
Next ii
End Sub

复制代码

ning84 · 发表于 2022-12-16 15:33

再做优化

Sub l()
Dim Str
Dim oDate As Date
Str = "年月日正则分离2022年05月09日/See Me"
Debug.Print IsDate(DateExp(Str))
oDate = DateExp(Str)
Stop
End Sub
Function DateExp(Str)
Dim oRegExp As RegExp
Dim oMatColl As MatchCollection
Dim oMatch As Match
Dim oSubMat As SubMatches
Dim RegStr
Set oRegExp = New RegExp
RegStr = "\d{2,4}(年|-|/|.)\d{1,2}(月|-|/|.)\d{1,4}(日|/||)"
oRegExp.Pattern = RegStr
Set oMatColl = oRegExp.Execute(Str)
DateExp = oMatColl.Item(0)
End Function

复制代码

ning84 · 发表于 2023-5-3 18:05

对于这种格式的 2023年05月17日周三，用left(str,11)跟简单一些。是否好用还需要测试。

Sub del()
Dim Str, oDate As Date
Dim Rng As Range
Set Rng = Selection
For ii = 1 To Rng.Rows.Count
Str = Left(Rng(ii, 1), 11)
oDate = Str
Debug.Print Str, oDate
If IsDate(oDate) Then
Rng(ii, 1) = oDate
End If
Next ii
End Sub

复制代码

goldowl2011 · 发表于 2023-6-2 16:32

标记学习，谢谢分享代码。

18380222672 · 发表于 2023-6-2 16:45

标记，谢谢分享

ning84 · 发表于 2024-4-14 16:30

在此学习2022年的帖子， 2022-11-15 21:44对正则确实一点都不懂。

Sub ll2()
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As Match '成功匹配的对象
Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Dim RegStr, Str
Dim oDate As Date
Set oRegExp = New RegExp
RegStr = "([\d]{2,4})([\d]{1,2})([\d]{1,2})_([\d]{1,2})([\d]{1,2})([\d]{1,2})"
Str = "C:\隧道南图书馆20240323\IMG_20240323_083145279.jpg "
oRegExp.Pattern = RegStr
Set oMatColl = oRegExp.Execute(Str)
Set oMatch = oMatColl.Item(0)
Debug.Print oMatch.Value
Debug.Print oMatch.SubMatches.Count
With oMatch.SubMatches
Str = .Item(0) & "/" & .Item(1) & "/" & .Item(2)
Str = Str & " " & .Item(3) & ":" & .Item(4) & ":" & .Item(5)
oDate = Str
End With
Debug.Print oDate
End Sub
'''
Sub ll3()
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As Match '成功匹配的对象
Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Dim RegStr, Str
Dim oDate As Date
Set oRegExp = New RegExp
RegStr = "([\d]{2,4})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})"
Str = "C:\隧道南图书馆20240323\Screenshot_2024-03-23-08-43-11-439_com.tencent.map.jpg"
oRegExp.Pattern = RegStr
Set oMatColl = oRegExp.Execute(Str)
Set oMatch = oMatColl.Item(0)
Debug.Print oMatch.Value
Debug.Print oMatch.SubMatches.Count
With oMatch.SubMatches
Str = .Item(0) & "/" & .Item(1) & "/" & .Item(2)
Str = Str & " " & .Item(3) & ":" & .Item(4) & ":" & .Item(5)
oDate = Str
End With
Debug.Print oDate
End Sub

复制代码

blackttea1 · 发表于 2024-4-15 08:44

标记，谢谢分享

ning84 · 发表于 2024-4-18 10:13

本帖最后由 ning84 于 2024-4-18 10:46 编辑

学习正则方法近十年，还是一知半解。
笨办法学习，一遍一遍做题。

Sub ll()
Dim Arr(3)
Arr(0) = "IMG20231121102939.jpg"
Arr(1) = "IMG_20231121_100743630.jpg"
Arr(2) = "IMG_20231121_101338692.jpg"
Arr(3) = ".Screenshot_2024-01-18-06-15-33-976_com.autonavi.minimap.jpg"
Dim RegArr(2)
RegArr(0) = "([\d]{2,4})([\d]{1,2})([\d]{1,2})_([\d]{1,2})([\d]{1,2})([\d]{1,2})"
RegArr(1) = "([\d]{2,4})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})"
RegArr(2) = "([\d]{2,4})([\d]{1,2})([\d]{1,2})([\d]{1,2})([\d]{1,2})([\d]{1,2})"
Dim Str
For ii = 0 To UBound(RegArr)
For jj = 0 To UBound(Arr)
Str = RegYYYYMMDDHMS(RegArr(ii), Arr(jj))
If IsDate(Str) Then
Debug.Print Arr(jj), Str
End If
Next jj
Next ii
End Sub
Function RegYYYYMMDDHMS(RegStr, oStr)
Dim Str
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Set oRegExp = New RegExp
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As Match '成功匹配的对象
'Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Dim oDate As Date
'''
oRegExp.Pattern = RegStr
Set oMatColl = oRegExp.Execute(oStr)
If oMatColl.Count = 0 Then
RegYYYYMMDDHMS = oStr
Exit Function
End If
Set oMatch = oMatColl.Item(0)
With oMatch.SubMatches
Str = .Item(0) & "/" & .Item(1) & "/" & .Item(2)
Str = Str & " " & .Item(3) & ":" & .Item(4) & ":" & .Item(5)
End With
If IsDate(Str) Then
oDate = Str
RegYYYYMMDDHMS = oDate
End If
End Function

复制代码

''''

Function RegYYYYMMDDHMS(oStr)
Dim RegArr(2)
RegArr(0) = "([\d]{2,4})([\d]{1,2})([\d]{1,2})_([\d]{1,2})([\d]{1,2})([\d]{1,2})"
RegArr(1) = "([\d]{2,4})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})-([\d]{1,2})"
RegArr(2) = "([\d]{2,4})([\d]{1,2})([\d]{1,2})([\d]{1,2})([\d]{1,2})([\d]{1,2})"
Dim Str
Dim oRegExp As RegExp 'VB使用正则表达式匹配模式的主要对象
Set oRegExp = New RegExp
Dim oMatColl As MatchCollection '是集合对象，包含有关匹配字符串的信息，该对象包含每个成功匹配的 Match 对象
Dim oMatch As Match '成功匹配的对象
'Dim oSubMat As SubMatches '匹配对象所匹配结果的子项
Dim oDate As Date
'''
For ii = 0 To UBound(RegArr)
oRegExp.Pattern = RegArr(ii)
Set oMatColl = oRegExp.Execute(oStr)
If oMatColl.Count > 0 Then
Exit For
End If
Next ii
If oMatColl.Count = 0 Then
RegYYYYMMDDHMS = oStr
Exit Function
End If
Set oMatch = oMatColl.Item(0)
With oMatch.SubMatches
Str = .Item(0) & "/" & .Item(1) & "/" & .Item(2)
Str = Str & " " & .Item(3) & ":" & .Item(4) & ":" & .Item(5)
End With
If IsDate(Str) Then
oDate = Str
RegYYYYMMDDHMS = oDate
End If
End Function
Sub ll()
Dim Sht As Worksheet
Dim Rng As Range
Set Rng = Selection.CurrentRegion
Set Sht = Rng.Parent
With Sht
Set Rng = .Cells(Rng.Row, "B").Resize(Rng.Rows.Count)
End With
'Debug.Print Rng.Address
For ii = 1 To Rng.Rows.Count
Rng(ii, 4) = RegYYYYMMDDHMS(Rng(ii, 1))
Next ii
End Sub

复制代码

		自动登录	找回密码
密码			免费注册

[分享] 用正则方法分离日期的几种方法小结。