CODEWITHSUNDEEP
excelexcel-formula
Heba
Heba

Reputation: 45

Extracting date from from text, not in correct format

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

Upvotes: 0

Views: 139

Answers (1)

JvdV
JvdV

Reputation: 75990

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

Upvotes: 1

Related Questions