'extract a date from a cell containing text string in excel

For the Twelve Months Ending Thursday, June 30, 2016

I have the above text in excel and I need an excel formula to extract ONLY the date June 30, 2016. The formulas I've researched were all for numerical dates (i.e. 06/30/2016). Would anyone know how to do this? Thanks in advance!



Solution 1:[1]

The formula looks like this:

=MID(A1,FIND(",",A1)+1,100)

It does a FIND to figure out where the first comma is, and then returns everything beyond that point.

Solution 2:[2]

Extracts a Date as a numeric. Then just format it as a date.

=DATEVALUE(RIGHT(B20,LEN(B20)-FIND(",",B20)-1))

Solution 3:[3]

=DATE(RIGHT(A1,4),MONTH(DATEVALUE(LEFT(MID(A1,FIND(",",A1)+2,LEN(A1)),FIND(" ",MID(A1,FIND(",",A1)+2,LEN(A1)-1)))&1)),LEFT(RIGHT(A1,8),2))

Hopefully will work for either US or UK locale.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 durbnpoisn
Solution 2 Brian
Solution 3 pnuts