Parse Date From File Title

[jenner85]

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 solution]

[FinnCharlton · Accepted solution]

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

[DataNath · Accepted solution]

@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 solution]

Add RegEx Tool with the Formula :

Add formula Tool and add a new column - Date :

[jenner85]

Can you help parse the date out of this format please? 

CPP_8549CONS_TDMBNAConsumer-20220501.xlsx

[Pranay18]

Hi Jenner,

Please see the solution below: