'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 |