Extracting date from from text, not in correct format

0
votes

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

enter image description here

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

1
excelexcel-formula

1 Answers

1
votes

I think you could try, if one has O365:

enter image description here

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