Parse Date From File Title

Question

Hi - I am terrible with regex and need to  be able to pull the date out of all of these file names, can anyone help me please? I then need to turn it into first of the month format (2022-04-01). Thanks!

Rewards Merchandise APR2022_Report.csv
Choice Rewards Merchandise May2022_Report.csv
Rewards Merchandise May2022_Report.csv
Rewards Merchandise O May2022_Report.csv
Choice Rewards Merchandise Jun2022_Report.csv
Choice Rewards Merchandise_O_Jun2022_Report.csv
Rewards Merchandise Jun2022_Report.csv
Choice Rewards Merchandise Jul2022_Report.csv
Rewards Merchandise_O_Jul2022_Report.csv
Choice Rewards Merchandise AUG2022_CI-0000015127 Report.csv
Rewards Merchandise AUG2022_CI-0000015124 Report.csv
Choice Rewards Merchandise SEP2022 Report.csv
Rewards Merchandise SEP2022 Report.csv
Rewards Merchandise SEP2022_Report.csv
Choice Rewards Merchandise OCT2022_CI-0000015810_Report.csv

MilindG · Accepted Answer

FinnCharlton · Accepted Answer

@jenner85 , You can add one more tool to get the date in the format you want:

DataNath · Accepted Answer

@jenner85 here's one way you could go about it - just looks for a pattern of 3 letters followed by 4 digits immediately and then dateparses it:

If you'll ever have extra parts of your file names that may throw this off let us know as it'll probably require a more nuanced approach.

Pranay18 · Accepted Answer

Add RegEx Tool with the Formula :

Add formula Tool and add a new column - Date :