CODEWITHSUNDEEP
regexexcelvba
ilyaw77
ilyaw77

Reputation: 1047

match date pattern in the string vba excel

Edit: Since my string became more and more complicated looks like is the only way. I do not have a lot experience in that and your help is much appreciated.

Basically from what I read on the web I construct the following exp to try matching occurrence in my sample string:

"My very long long string 12Mar2012 is right here 23Apr2015" [0-9][0-9] + [a-zA-Z] + [0-9][0-9][0-9][0-9]

and trying this code. I do not have any match. Any good link on regexp tutorial much appreciated.

  Dim re, match, RegExDate
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(^[0-9][0-9] + [a-zA-Z] + [0-9][0-9][0-9][0-9]$)"
    re.Global = True

    For Each match In re.Execute(str)
        MsgBox match.Value
        RegExDate = match.Value
        Exit For
    Next

Thank you

Upvotes: 1

Views: 4938

Answers (3)

brettdj
brettdj

Reputation: 55682

This code validates the actual date from the Regexp using DateValuefor robustness

Sub Robust()
Dim Regex As Object
Dim RegexMC As Object
Dim RegexM As Object
Dim strIn As String
Dim BDate As Boolean
strIn = "My very long long string 12Mar2012 is right here 23Apr2015 and 30Feb2002"
Set Regex = CreateObject("vbscript.regexp")
With Regex
 .Pattern = "(([0-9])|([0-2][0-9])|([3][0-1]))(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\d{4})"
 .Global = True
If .test(strIn) Then
   Set RegexMC = .Execute(strIn)
   On Error Resume Next
   For Each RegexM In RegexMC
   BDate = False
   BDate = IsDate(DateValue(RegexM.submatches(0) & " " & RegexM.submatches(4) & " " & RegexM.submatches(5)))
   If BDate Then Debug.Print RegexM
   Next
   On Error GoTo 0
End If
End With
End Sub

Upvotes: 3

ilyaw77
ilyaw77

Reputation: 1047

thanks for all your help !!! I managed to solve my problem using this simple code. 

Dim rex As New RegExp
Dim dateCol As New Collection
rex.Pattern = "(\d|\d\d)(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\d{4})?"
rex.Global = True

For Each match In rex.Execute(sStream)
    dateCol.Add match.Value
Next

Just note that on my side I'm sure that I got valid date in the string so the reg expression is easy.

thnx Ilya

Upvotes: 1

NickSlash
NickSlash

Reputation: 5077

The following is a quick attempt I made. It's far from perfect.

Basically, it splits the string into words. While looping through the words it cuts off any punctuation (period and comma, you might need to add more).

When processing an item, we try to remove each month name from it. If the string gets shorter we might have a date.

It checks to see if the length of the final string is about right (5 or 6 characters, 1 or 2 + 4 for day and year)

You could instead (or also) check to see that there all numbers.

Private Const MonthList = "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC"
Public Function getDates(ByVal Target As String) As String
Dim Data() As String
Dim Item As String
Dim Index As Integer
Dim List() As String
Dim Index2 As Integer
Dim Test As String
Dim Result As String

    List = Split(MonthList, ",")
    Data = Split(Target, " ")
    Result = ""

    For Index = LBound(Data) To UBound(Data)
        Item = UCase(Replace(Replace(Data(Index), ".", ""), ",", ""))
        For Index2 = LBound(Data) To UBound(Data)
            Test = Replace(Item, List(Index2), "")
            If Not Test = Item Then
                If Len(Test) = 5 Or Len(Test) = 6 Then
                    If Result = "" Then
                        Result = Item
                    Else
                        Result = Result & ", " & Item
                    End If
                End If
            End If
        Next Index2
    Next
    getDates = Result
End Function

Upvotes: 0

Related Questions