Get 2 Date from String

[zezo2021]

Hello friends;

How can I extract these 2 dates from this string?

11/01/2021 through 11/09/2021 Projected

 

[theDBguy]

With that particular example, you could use the Split() function.

 

[sxschech]

Here is another idea using a vba function if all you want is to have all the dates extracted into a string, however, as theDBguy pointed out, if you want to use it in a specific way, would still need the split function. The function below can return all matches of a given pattern. It is set up to default with a date pattern, but can also use it to pull out any pattern that appears in a string if you are able to construct a proper regex pattern. Code below has a few examples in the comments. I added an extra date in the Example demo for illustration.

Function RegexReturnAllMatches(strInput As String, Optional pttrn As String, Optional sepchar As String = " ") As String
'Extract all Matches from a string
'Default Pattern is date formatted as digits followed by slash dot or dash
'   ** Currently does not test for valid date only a date like pattern **
'Can provide any other pattern if want to use for non date purposes
'Can change the sep char from space to any other character such as comma
'Example:
'--Default
' ? RegexReturnAllMatches("11/01/21 through 11/09/2021 Code Projected 12-29-2021 Actual through year end")
' 11/01/21 11/09/2021 12-29-2021
'--Put your own pattern and sep char
' ? RegexReturnAllMatches("11/01/21 through 11/09/2021 Code Projected 12/29/2021 Actual through year end", "[\d]+[\/\-\.][\d]+[\/\-\.][\d]+", ",")
' 11/01/21,11/09/2021,12/29/2021
'--Exclude Year
' ? RegexReturnAllMatches("11/01/21 through 11/09/2021 Code Projected 12/29/2021 Actual through year end", "[\d]+[\/\-\.][\d]+", ",")
' 11/01,11/09,12/29
'--Find the word THROUGH
' ? RegexReturnAllMatches("11/01/21 through 11/09/2021 Code Projected 12/29/2021 Actual through year end", "through", ",")
' through , through
'--Extract words containing 4 letters
'? RegexReturnAllMatches("11/01/21 through 11/09/2021 Code Projected 12-29-2021 Actual through year end","\b[a-z]{4}\b")
'Code year
'
'Based on:
'http://stackoverflow.com/questions/7929205/vba-regular-expression-to-match-date
'https://stackoverflow.com/questions/44979363/excel-vba-regex-function-that-returns-multiple-matches-into-a-single-cell
'20211104
    Dim RE, Match
    Dim toSlash As String
    Dim arrayMatches()
    Dim i As Long
    
    Set RE = CreateObject("vbscript.regexp")
    
    With RE
        .Global = True
        .Multiline = True
        .IgnoreCase = True
        If Len(pttrn) = 0 Then
            .Pattern = "[\d]+[\/\-\.][\d]+[\/\-\.][\d]+"
        Else
            .Pattern = pttrn
        End If
        If .Test(strInput) Then
            For Each Match In .Execute(strInput)
                ReDim Preserve arrayMatches(i)
                arrayMatches(i) = Match.Value
                i = i + 1
            Next
        End If
    End With
    
    RegexReturnAllMatches = Join(arrayMatches, sepchar)
End Function

 

[oleronesoftwares]

Mid("11/01/2021 through 11/09/2021 Projected", 1,11) // returns 11/01/2021
Mid("11/01/2021 through 11/09/2021 Projected", 19,11)// returns 11/09/2021