extract a date from a cell containing text string in excel

2
votes

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!

3
exceldateextractformula
Do a substring to find the index of the first comma. Use that to pull all the RIGHT() text for everything after that index. - durbnpoisn
Are all the cells in this format? Or are there other words before and/or after the date. - Scott Craner
It's always this format but the nth month, day and date changes. For example in July it will say, For the First Month Ending Sunday, July 31, 2016. - Eileen
I'm not sure I fully understand but DATEVALUE() works on text. - Brian
@pnuts No problem, that's fine. I just understood 'The formulas I've researched were all for numerical dates' to mean that she wants a formula to work on text but (and, perhaps, here I assumed) return a date, since she wants to extract a date. :-) - Brian

3 Answers

0
votes

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.

0
votes

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

=DATEVALUE(RIGHT(B20,LEN(B20)-FIND(",",B20)-1))
0
votes
=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.