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

bran8989

New Member
Joined
Sep 14, 2018
Messages
23
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!!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,551
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

bran8989

New Member
Joined
Sep 14, 2018
Messages
23
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

Active Member
Joined
Nov 21, 2016
Messages
279
Hello,

try this code in an empty sheet first:

Code:
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

New Member
Joined
Sep 14, 2018
Messages
23
that works but how do I apply it to my text in cell A1? It seems to only work for that constant text..
 

Watch MrExcel Video

Forum statistics

Threads
1,127,197
Messages
5,623,310
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top