SOLVED

How do I get just the date using regex

%3CLINGO-SUB%20id%3D%22lingo-sub-2839997%22%20slang%3D%22en-US%22%3EHow%20do%20I%20get%20just%20the%20date%20using%20regex%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2839997%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20some%20regex%20(%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwhy-does-the-following-pattern-work-with-one-of-my-udf-vs-the%2Fm-p%2F2804829%22%20target%3D%22_self%22%3Elike%20here%3C%2FA%3E)%20to%20get%20just%20the%20date%20from%20following%20string%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3Er%20%3D%204834%2C%20ReportDate%20%3D%2010%2F11%2F2021%2012%3A00%3A00%20AM%2C%20Data%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edesired%20result%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CPRE%3E10%2F11%2F2021%3C%2FPRE%3E%3CP%3ETried%3A%3C%2FP%3E%3CPRE%3E%3DgetTargetValueV2(J8%2C%22ReportDate%20%3D%20(%5Cw%2B)%22)%26nbsp%3B%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rodsan724_0-1634078499598.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316925i4229D064C6487DE4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rodsan724_0-1634078499598.png%22%20alt%3D%22rodsan724_0-1634078499598.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2839997%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2840295%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20get%20just%20the%20date%20using%20regex%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2840295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20it%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DgetTargetValueV2(A1%2C%22ReportDate%20%3D%20(%5Cd%7B2%7D%2F%5Cd%7B2%7D%2F%5Cd%7B4%7D)%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2840299%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20get%20just%20the%20date%20using%20regex%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2840299%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20instead%20of%20using%20the%20pattern%20in%20the%20formula%2C%20place%20the%20following%20UDF%20on%20a%20Standard%20Module%20and%20then%20you%20can%20simply%20use%20the%20formula%20%3CSTRONG%3E%3DgetDate(A1)%3C%2FSTRONG%3E%20on%20the%20worksheet%20and%20then%20change%20the%20format%20of%20the%20formula%20cell%20to%20Date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20getDate(ByVal%20content%20As%20String)%20As%20Date%0ADim%20Matches%20As%20Object%0AWith%20CreateObject(%22VBScript.RegExp%22)%0A%20%20%20%20.Global%20%3D%20True%0A%20%20%20%20.ignorecase%20%3D%20True%0A%20%20%20%20.pattern%20%3D%20%22%5Cd%7B2%7D%2F%5Cd%7B2%7D%2F%5Cd%7B4%7D%22%0A%20%20%20%20If%20.test(content)%20Then%0A%20%20%20%20%20%20%20%20Set%20Matches%20%3D%20.Execute(content)%0A%20%20%20%20%20%20%20%20getDate%20%3D%20Matches(0)%0A%20%20%20%20End%20If%0AEnd%20With%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

 

I'm trying to use some regex (like here) to get just the date from following string

 

r = 4834, ReportDate = 10/11/2021 12:00:00 AM, Data

 

desired result:  

10/11/2021

This seems to work:

=getTargetValueV2(A1,"ReportDate = (\w+/\w+/\w+)")

 

rodsan724_0-1634080245615.png

 

 

 

2 Replies

@rodsan724 

Please try it like this...

 

=getTargetValueV2(A1,"ReportDate = (\d{2}/\d{2}/\d{4})")
best response confirmed by rodsan724 (Contributor)
Solution

@rodsan724 

Or instead of using the pattern in the formula, place the following UDF on a Standard Module and then you can simply use the formula =getDate(A1) on the worksheet and then change the format of the formula cell to Date.

 

 

Function getDate(ByVal content As String) As Date
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = True
    .ignorecase = True
    .pattern = "\d{2}/\d{2}/\d{4}"
    If .test(content) Then
        Set Matches = .Execute(content)
        getDate = Matches(0)
    End If
End With
End Function