Extract Just Dates From Text String

[Justinian]

I am trying to extract dates from a cell like this: "Completed: 7/20/20 with primary contingency"

To do so, I used this formula with success:

=MID(A2,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)),LOOKUP(1,0*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789",1)),LEN(A2)+1)))

The problem is that not all text strings have just one set of numbers so this MID formula will not work. For example, a cell might say this: "Completed: 7/20/20 with primary contingency, G2 upper" or "Progress: U2, scheduled: 7/20/20 with primary contingency."

How do I get Excel to extract just the date from a cell and exclude all numbers that are not part of that date?

 

[DanteAmor]

Try this:

varios 06ago2020.xlsm
	A	B
1
2	Completed: 7/20/20 with primary contingency, G2 upper	7/20/20
3	Completed: 7/20/20 with primary contingency	7/20/20
4	Progress: U2, scheduled: 7/20/20 with primary contingency.	7/20/20
xd

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(MID(A2,FIND("/",A2)-2,8))

 

[Eric W]

You can try

Book1
	A	B
1	Progress: U2, scheduled: 7/20/20 with primary contingency.	7/20/2002
Sheet5

Cell Formulas
Range	Formula
B1	B1	=AGGREGATE(15,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),{6,7,8,9,10})+0,1)

This finds the largest number in the string, which should be your date.

Edit:
I just noticed an error, the formula should be:
=AGGREGATE(14,6,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),{6,7,8,9,10})+0,1)

 

[alansidman]

Using Power Query/Get and Transform which is on the Data Tab, here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BetweenDelimiters(_, ": ", " "), type text}})
in
    #"Extracted Text Between Delimiters"

If you are unfamiliar with PQ, then look at the links in my signature

 

[lrobbo314]

A VBA option.

Book2
	D	E
28	Completed: 7/20/20 with primary contingency	7/20/20
Sheet1

Cell Formulas
Range	Formula
E28	E28	=ExtractDate(D28)

Function ExtractDate(d As String) As String
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "((?:\d{1,2})\/?)"
    Set m = .Execute(d)
    For Each x In m
        ExtractDate = ExtractDate & x
    Next x
End With
End Function

 

[DanteAmor]

If you have the year in 4 digits, then:

varios 06ago2020.xlsm
	A	B
1
2	Completed: 7/20/20 with primary contingency, G2 upper	7/20/20
3	Completed: 7/20/2020 with primary contingency	7/20/2020
4	Progress: U2, scheduled: 7/20/20 with primary contingency.	7/20/20
5	Progress: U2, scheduled: 11/20/20 with primary contingency.	11/20/20
6	Progress: U2, scheduled: 11/5/2020 with primary contingency.	11/5/2020
7	Progress: U2, scheduled: 1/1/20 with primary contingency.	1/1/20
8	Progress: U2, scheduled: 1/1/2020 with primary contingency.	1/1/2020
xd

Cell Formulas
Range	Formula
B2:B8	B2	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(A2,SEARCH("/",A2)-2,LEN(A2)))," ",REPT(" ",99)),99))

 

[lrobbo314]

If you wanted to go the VBA route, the amended code below is more robust.

Function ED(d As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\b(?:\d{1,2}\/)+\d{2,4}\b"
    ED = .Execute(d)(0)
End With
End Function

 

[Justinian]

Holy moly, that is a lot of good solutions.

Thank you, everyone!

 

[Justinian]

Sorry, one more question. When I run the first formula shown below, I get a date of 07/28/20. When I apply a filter, it does not show up under July but when I type in 7/28, it does show up under July in the filter. Any idea why this is happening?

=TRIM(MID(K3,FIND("/",K3)-2,8))

 

[DanteAmor]

The result of the formula is a text, it may have to be converted to date. try the following:

Dante Amor
	A	B
1
2	Completed: 7/20/20 with primary contingency, G2 upper	07/20/2020
3	Completed: 7/3/20 with primary contingency	07/03/2020
4	Progress: U2, scheduled: 10/20/20 with primary contingency.	10/20/2020
5	Completed: 7/2/19 with primary contingency	07/02/2019
Hoja3

Cell Formulas
Range	Formula
B2:B5	B2	=(SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/","")&"/"&TRIM(MID(A2,FIND("/",A2)-2,2))&"/"&TRIM(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,2)))+0