Retrieving Variable dates from Filenames
Results 1 to 5 of 5

Thread: Retrieving Variable dates from Filenames
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Retrieving Variable dates from Filenames

    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.

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,780
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieving Variable dates from Filenames

    Hello Bobbo421,

    Where do these file names come from?

    Will they always be PDF files?
    Sincerely,
    Leith Ross

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,123
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Retrieving Variable dates from Filenames

    Is the date always in the format dd mmm(m) yy?
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieving Variable dates from Filenames

    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.

  5. #5
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,780
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retrieving Variable dates from Filenames

    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)

    Code:
    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

    1. Copy the macro with Ctrl+C.
    2. Open the workbook and use Alt+F11 to open the VB Editor.
    3. Use ALT+I to display the Insert Menu.
    4. Press the M key to add a new Module.
    5. Paste the macro into the Module with Ctrl+V.
    6. Save the macro using Ctrl+S
    Sincerely,
    Leith Ross

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •