none
Extract a date from a string. RRS feed

  • Question

  • I have a string, say

    12345xyz#21/4/2015#hyr1234

    The date lies between the two # characters but the amount of characters could vary as in #1/4/2015#

    There are a variable number of characters before and after the date and could be letters or numbers. but there will only be the two # characters that enclose the date.

    I need to extract the date.

    What is the correct method for doing this?

    Thanks

    Andy

    Tuesday, July 21, 2015 12:48 PM

Answers

  • cdate(mid(left("12345xyz#21/4/2015#hyr1234",instrrev("12345xyz#21/4/2015#hyr1234","#")-1),instr("12345xyz#21/4/2015#hyr1234","#")+1))
    • Proposed as answer by André Santo Tuesday, July 21, 2015 1:17 PM
    • Marked as answer by AndyNakamura Tuesday, July 21, 2015 1:19 PM
    Tuesday, July 21, 2015 12:59 PM
  • Thanks for explanation and example. There is a danger of no date being present. I better check for that first.

    The procedure that I provide to you will handle the circumstance when no date is present.  Give it a try.
    • Marked as answer by AndyNakamura Wednesday, July 22, 2015 6:32 AM
    Tuesday, July 21, 2015 4:55 PM

All replies

  • cdate(mid(left("12345xyz#21/4/2015#hyr1234",instrrev("12345xyz#21/4/2015#hyr1234","#")-1),instr("12345xyz#21/4/2015#hyr1234","#")+1))
    • Proposed as answer by André Santo Tuesday, July 21, 2015 1:17 PM
    • Marked as answer by AndyNakamura Tuesday, July 21, 2015 1:19 PM
    Tuesday, July 21, 2015 12:59 PM
  • Thanks Alphonse,

    Works a treat. Just got to figure out how it works now!

    Tuesday, July 21, 2015 1:21 PM
  • Thanks Alphonse,

    Works a treat. Just got to figure out how it works now!

    Andy,

    Does your string contain one date only?  The code above works fine, if so.  However, if you are needing to extract more than one date from a string, the procedure won't manage that.

    Another question.  For any given string that is to be tested, is there the possibility that a date won't be in the string?  Again, if so, the above procedure will fail.

    To explain how it works:

    Cdate is a Type Conversion Function.  It ensures the output value will be a Date/Time data type.  However, the input value must be capable of being evaluated as a date.  If it can't, a type mismatch error will occur.  If the input value should be a null, an invalid use of null error will occur.

    Mid() is a function for extracting substrings.  It has three arguments.  The first argument is the string to search, the second argument is the starting position of the substring within the string, and the last argument is the ending position of the substring within the string.

    InStr and InStrRev are functions to locate the position of a substring within a string.  InStr searches from left to right, and InStrRev searches from right to left.  In your circumstance, the two procedures are finding the first and last position of the pound sign (#).

    A more robust method would be to use Regular Expression matching as shown below:

    Function TestExtractDate()
        Dim strTest As String
        Dim varResult As Variant
        
        strTest = "12345xyz#21/4/2015#hyr1234"
        
        varResult = ExtractDate(strTest)
        
        If (Not IsNull(varResult)) Then
            Debug.Print varResult
        Else
            MsgBox "A date was not found", vbInformation, "Status"
        End If
        
    End Function
    
    Function ExtractDate(strTest As String) As Variant
    
        'Purpose: Searches for a date within in a string
        'Note: Requires a reference to Microsoft VBScript Regular Expressions 5.5
        'Return: either the date found or null
        
        On Error GoTo Err_Process
        
        Dim objRE As RegExp
        Dim objMatch As Match
        Dim objMatches As MatchCollection
        Dim varReturn As Variant
        
        varReturn = Null
        
        Set objRE = New RegExp
        
        objRE.Pattern = "(#)(\d{1,2}/\d{1,2}/\d{4})(#)"
        objRE.Global = False
        
        Set objMatches = objRE.Execute(strTest)
        
        If (objMatches.Count <> 0) Then
            Set objMatch = objMatches(0)
            varReturn = objMatch.SubMatches(1)
        End If
        
    Exit_Process:
        Set objRE = Nothing
        Set objMatch = Nothing
        Set objMatches = Nothing
        ExtractDate = varReturn
        Exit Function
    
    Err_Process:
        'Your error handler
        Resume Exit_Process
        
    End Function


    If you need to search for more than one date in the string, the above procedure would need some minor modification.

    Best Regards,





    Tuesday, July 21, 2015 2:21 PM
  • Happy to help. To figure it out, just break it apart to each function and look up in help any function that you are not familiar with.
    Tuesday, July 21, 2015 2:39 PM
  • Thanks for explanation and example. There is a danger of no date being present. I better check for that first.
    Tuesday, July 21, 2015 4:48 PM
  • Thanks for explanation and example. There is a danger of no date being present. I better check for that first.

    The procedure that I provide to you will handle the circumstance when no date is present.  Give it a try.
    • Marked as answer by AndyNakamura Wednesday, July 22, 2015 6:32 AM
    Tuesday, July 21, 2015 4:55 PM
  • Yes, I will give it a go.
    Wednesday, July 22, 2015 6:32 AM