VBA extract date before keywords

[Heather515]

Need help with extracting date from text strings. I need to extract the date before a keyword. Data and results are shown below. Could somebody help me to write a function or VBA macro. Thanks.

data​	result​
NoteNoteNote6/11/2010NoteNote,NoteNote9/22/2012NoteNoteTHISNoteNote,NoteNote4/5/2019NoteNote​	9/22/2012​
NoteNote5/22/2018NoteNoteTHISNoteNote,NoteNoteNote6/18/2019NoteNote,NoteNote4/18/2019NoteNote​	5/22/2018​
NoteNoteNote8/18/2018NoteNote,NoteNote4/20/2019NoteNote,NoteNote9/10/2012NoteNoteTHISNoteNote​	9/10/2012​

 

[Tetra201]

If you are OK with a formula, try the following (the result should be formatted as m/d/yyyy):

=AGGREGATE(14,6,--MID(A2,SEARCH("NoteNoteTHIS",A2)-{8,9,10},{8,9,10}),1)

 

[Phuoc]

Or try:

=LOOKUP(10^35,--MID(A2,SEARCH("NoteNoteTHIS",A2)-{8,9,10},{8,9,10}))

 

[Heather515]

If you are OK with a formula, try the following (the result should be formatted as m/d/yyyy):

=AGGREGATE(14,6,--MID(A2,SEARCH("NoteNoteTHIS",A2)-{8,9,10},{8,9,10}),1)

Thanks. In my data, "Note" means different characters. I can only use "THIS" as the keyword to extract the date.

 

[Heather515]

Or try:

=LOOKUP(10^35,--MID(A2,SEARCH("NoteNoteTHIS",A2)-{8,9,10},{8,9,10}))

Thanks. In my data, "Note" means different characters. I can only use "THIS" as the keyword to extract the date.

 

[Tetra201]

Thanks. In my data, "Note" means different characters. I can only use "THIS" as the keyword to extract the date.

Here is a modified formula based on the solution offered in Post #2:

=AGGREGATE(14,6,--MID(A2,LOOKUP(2^1023,--MID(A2,ROW(INDIRECT("1:"&SEARCH("THIS",A2))),1),ROW(INDIRECT("1:"&SEARCH("THIS",A2))))-{7,8,9},{8,9,10}),1)

If you version of Excel doesn't have the AGGREGATE function, here is a modified formula based on the solution offered in Post #3:

=LOOKUP(2^1023,--MID(A2,LOOKUP(2^1023,--MID(A2,ROW(INDIRECT("1:"&SEARCH("THIS",A2))),1),ROW(INDIRECT("1:"&SEARCH("THIS",A2))))-{7,8,9},{8,9,10}))

 

[Heather515]

Here is a modified formula based on the solution offered in Post #2:

=AGGREGATE(14,6,--MID(A2,LOOKUP(2^1023,--MID(A2,ROW(INDIRECT("1:"&SEARCH("THIS",A2))),1),ROW(INDIRECT("1:"&SEARCH("THIS",A2))))-{7,8,9},{8,9,10}),1)

If you version of Excel doesn't have the AGGREGATE function, here is a modified formula based on the solution offered in Post #3:

=LOOKUP(2^1023,--MID(A2,LOOKUP(2^1023,--MID(A2,ROW(INDIRECT("1:"&SEARCH("THIS",A2))),1),ROW(INDIRECT("1:"&SEARCH("THIS",A2))))-{7,8,9},{8,9,10}))

Thank you very much. I really appreciate it! It works for the sample data but doesn't work for my raw data. Maybe the structure is not as simple as the sample data I submitted. My apology! I'll check the raw data to see why the formulas don't work.

 

[Peter_SSs]

You could try this user-defined function.
You will note that the dates are a bit mixed up between month and day for me as I am not on a US date format system. If you are then I think this should work, but if not a tweak can be made.
Also note that this function is doing a case-sensitive search for the keyword. Again a tweak can be made if you want it to search for upper/lower/mixed case matches.

Function GetDate(s As String, Optional keyWord As String = "THIS") As Variant
  With CreateObject("VBScript.Regexp")
    .Pattern = "\d{1,2}\/\d{1,2}\/\d{4}(?=\D*" & keyWord & ")"
    GetDate = vbNullString
    If .Test(s) Then GetDate = CDate(.Execute(s)(0))
  End With
End Function

Heather515.xlsm
	A	B
1	data	result
2	NoteNoteNote6/11/2010NoteNote,NoteNote9/22/2012NoteNoteTHISNoteNote,NoteNote4/5/2019NoteNote	22/9/2012
3	NoteNote5/22/2018NoteNoteTHISNoteNote,NoteNoteNote6/18/2019NoteNote,NoteNote4/18/2019NoteNote	22/5/2018
4	NoteNoteNote8/18/2018NoteNote,NoteNote4/20/2019NoteNote,NoteNote9/10/2012NoteNoteTHISNoteNote	9/10/2012
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=GetDate(A2)

 

[Heather515]

Here is a modified formula based on the solution offered in Post #2:

=AGGREGATE(14,6,--MID(A2,LOOKUP(2^1023,--MID(A2,ROW(INDIRECT("1:"&SEARCH("THIS",A2))),1),ROW(INDIRECT("1:"&SEARCH("THIS",A2))))-{7,8,9},{8,9,10}),1)

If you version of Excel doesn't have the AGGREGATE function, here is a modified formula based on the solution offered in Post #3:

=LOOKUP(2^1023,--MID(A2,LOOKUP(2^1023,--MID(A2,ROW(INDIRECT("1:"&SEARCH("THIS",A2))),1),ROW(INDIRECT("1:"&SEARCH("THIS",A2))))-{7,8,9},{8,9,10}))

I modified my data. Your formulas work perfect. Thanks.

 

[Heather515]

You could try this user-defined function.
You will note that the dates are a bit mixed up between month and day for me as I am not on a US date format system. If you are then I think this should work, but if not a tweak can be made.
Also note that this function is doing a case-sensitive search for the keyword. Again a tweak can be made if you want it to search for upper/lower/mixed case matches.

Function GetDate(s As String, Optional keyWord As String = "THIS") As Variant
  With CreateObject("VBScript.Regexp")
    .Pattern = "\d{1,2}\/\d{1,2}\/\d{4}(?=\D*" & keyWord & ")"
    GetDate = vbNullString
    If .Test(s) Then GetDate = CDate(.Execute(s)(0))
  End With
End Function

Heather515.xlsm
	A	B
1	data	result
2	NoteNoteNote6/11/2010NoteNote,NoteNote9/22/2012NoteNoteTHISNoteNote,NoteNote4/5/2019NoteNote	22/9/2012
3	NoteNote5/22/2018NoteNoteTHISNoteNote,NoteNoteNote6/18/2019NoteNote,NoteNote4/18/2019NoteNote	22/5/2018
4	NoteNoteNote8/18/2018NoteNote,NoteNote4/20/2019NoteNote,NoteNote9/10/2012NoteNoteTHISNoteNote	9/10/2012
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=GetDate(A2)

Thanks! This is what I want. It works perfect!