Search in String of Text to Find "June, 15, 2018"

[bran8989]

I have a sentence on Sheet1 in cell A1 that reads something like, "Andy went to the park on June 15, 2018 and he never came back".

How can I use VBA to extract that date from the sentence and put the date in Cell A2 on Sheet1.

Thanks!!

 

[Joe4]

Will it ALWAYS follow the same date format, i.e. mmmm dd, yyyy?
Can you give us a reasonable date ranges for the dates that will be showing?
Might the sentence also includes numbers in it?

It might be good to post some more examples, trying to capture different scenarios.

 

[bran8989]

Yes it will be in the mmmm, dd, yyyy format always.

The date ranges really could be any of the 12 months and the sentence could also include other numbers within it.

 

[Fennek]

Hello,

try this code in an empty sheet first:

Const Txt As String = "Andy went to the park on June 15, 2018 and he never came back"

Sub T_1()
With Range("A1:A5")
    .Value = Txt
    For Each c In .Cells
        For i = 1 To 12
        P1 = InStr(c, Application.GetCustomListContents(4)(i))
            If P1 > 0 Then
                M = i
                P2 = InStr(P1, c, ",")
                D = Split(Mid(c, P1, P2 - P1))(1)
                Y = Mid(c, P2 + 2, 4)
                iDate = VBA.DateSerial(Y, M, D)
                c.Offset(, 1) = iDate
            End If
        Next i
    Next c
End With
End Sub

regards

 

[bran8989]

that works but how do I apply it to my text in cell A1? It seems to only work for that constant text..