Retrieving Variable dates from Filenames

[Bobbo421]

Hello all,

Long time lurker, first time user....

So I am trying to retrieve dates from a lot of filenames. The dates and file names can be variable and I am at a loss... Ive been messing around with right() mid() Left() split(), but no luck... Here are a few examples for file names.

text - 12 jun 19.pdf (this is the standard for years, but people make mistakes with the naming convention)
text - 12 jun 19 .pdf
text - 12 jun 19.pdf
text - 12 june 19.pdf
text 12 jun 19.pdf
text - 12 jun 19 1.pdf
text - 12 jun 19 A.pdf

I know i might not be able to hit all of them, but id like to hit most of them. Let me know what you guys think.

 

[Leith Ross]

Hello Bobbo421,

Where do these file names come from?

Will they always be PDF files?

 

[Norie]

Is the date always in the format dd mmm(m) yy?

 

[Bobbo421]

The file names are locally generated. I'm going through each folder/subfolder with a criteria of "Test*.pdf"

Forgot to add that sometimes there's yyyy variable in the date as well.

 

[Leith Ross]

Hello Bobbo421,

Add a new VBA module to your workbook. Copy the code below and paste it into the module. You can the use this like an Excel formula, i.e. = ExtractDate(A2)

Global RegExp As Object


Function ExtractDate(ByVal TextDate As String) As Variant


    Dim RegExp  As Object
    
        Application.Volatile
        
        If RegExp Is Nothing Then
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.IgnoreCase = True
            RegExp.Pattern = "Text\s*\-?\s*(\d{1,2})\s+([a-z]{3,9})\s+(\d{2,4}).*"
        End If
        
        If RegExp.Test(TextDate) Then
            ExtractDate = RegExp.Replace(TextDate, "$1-$2-$3")
        End If
        
End Function

How to add a new VBA Module to your workbook

• Copy the macro with Ctrl+C.
• Open the workbook and use Alt+F11 to open the VB Editor.
• Use ALT+I to display the Insert Menu.
• Press the M key to add a new Module.
• Paste the macro into the Module with Ctrl+V.
• Save the macro using Ctrl+S