Reputation: 45
I'm trying to extract dates using excel from multiple documents, but the dates are only ever listed in the following format in a single cell
Please Pay By: Apr 10, 2019 Terms of payment: 30 Days
I have found the following resource, which is able to extract dates from text
https://www.extendoffice.com/documents/excel/4776-excel-extract-date-from-text.html
and I know i can use a vlookup formula and table
=DATE(RIGHT(A2,4),VLOOKUP(LEFT(A2,3),D2:E13,2,0),MID(A2,5,2))
But im having trouble combining these two methods to be able to extract the date from the following format:
Please Pay By: Apr 10, 2019 Terms of payment: 30 Days
(Didn't know how to get stack overflow to also show the gaps so have marked the format as code)
Any help would be greatly appreciated
Upvotes: 0
Views: 139
Reputation: 75990
I think you could try, if one has O365:
In formula B1
:
=DATE(--MID(A1,FIND(", ",A1)+2,4),MATCH(MID(TRIM(A1),16,3),TEXT(DATEVALUE("1/"&SEQUENCE(12)&"/2020"),"mmm"),0),MID(A1,FIND(", ",A1)-2,2))
If you don't have access to SEQUENCE
use:
=DATE(--MID(A1,FIND(", ",A1)+2,4),MATCH(MID(TRIM(A1),16,3),TEXT(DATEVALUE("1/"&{1,2,3,4,5,6,7,8,9,10,11,12}&"/2020"),"mmm"),0),MID(A1,FIND(", ",A1)-2,2))
Note, if you don't have O365, you need to accept through CtrlShiftEnter
Upvotes: 1