Extract the earliest date from text string

[Heather515]

Here is what data look like. Need to extract the earliest date from the text string in each cell. See example below. Need a VBA macro because the date size is very large. Thanks.

5/2/19 texttexttext 1/27/18 texttexttext 12/25/19 texttexttext	(result) 1/27/18
10/31/21notesnotes notes 5/1/18 texttext	(result) 5/1/18

 

[JEC]

I could be possible that your date format is different than mine, but you can try this first:

Function jec(cell As String)
   With CreateObject("VBscript.RegExp")
     .Global = True
     .Pattern = "(\d{1,2}\/\d{1,2}\/\d{2})"
     Set ar = .Execute(cell)
    
     m = 9999999
     For Each it In ar
        If DateValue(it) < m Then m = DateValue(it)
     Next
     jec = Format(m, "mm/dd/yy")
   End With
End Function

=Jec(A1)

 

[Heather515]

I could be possible that your date format is different than mine, but you can try this first:

Function jec(cell As String)
   With CreateObject("VBscript.RegExp")
     .Global = True
     .Pattern = "(\d{1,2}\/\d{1,2}\/\d{2})"
     Set ar = .Execute(cell)
   
     m = 9999999
     For Each it In ar
        If DateValue(it) < m Then m = DateValue(it)
     Next
     jec = Format(m, "mm/dd/yy")
   End With
End Function

=Jec(A1)

Thanks. This works perfectly.