Extracting Date

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
217
Good morning, I am trying to come up with an innovative way to extract the date from a file name.

We have a vendor that bills us using a spreadsheet file every month. I store the files in a folder and have power query join them from there.

The vendor names the files 103118MDS_Detail every month (when I say every they have so far but something changes in their data file consistently and PQ hates that).

I want to extract the date from the file name and create a column called Invoice date. What I had done in the past was extract everything before the M split the columns by 2 then add the / in so that PQ recognizes it as a date.

What concerns me is the fact that in January the vendor may name the file 13118MDS_Detail therefore ruining my code.

Is there a way to extract the date in a fool proof way that would account for vendor name changes?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi there

I don't think you will be able to have a foolproof way - but you could work backwards from the M and take previous 2 as year, previous 2 as day and the remaining 1 or 2 digits as the month. I suggest you contact the vendor direct and ask them what their rules are - or you may be able to arrange a consistent convention with them - after all you are a customer!
 
Upvote 0
I figured them keeping the data consistent was the best way, but I wanted to check as PQ seems to get better by the day. Wanted to make sure something had not passed me by. Thanks for the help!!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top