How to know if a string contains a date string

[hcova]

HI there.
I need to know if a date string (any date format) is part of a longer string. The problem is that the date string position can change its position in the longer one, so you can not use the left(), right() excel funtion to retrieve it. Moreover the date format separator can change too. For example "2021/08/26" or "2021-08-26".

For instance, I have the following string between " ":

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

As you can see there is a Date String (dd/mm/yyyy) embedded in this string. In this case the Date String is 12/08/2021 as it is shown in red below:

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

QUESTION 1: Is there any way to know if the Date String is embedded in the long string? With Excel functions or with a VBA code
QUESTION 2: Once identified if exist the Date String in the longer one, how can I read?

Any help is welcome.
Best regards
Hernán

 

[DanteAmor]

If the dates are between these characters
: date ^
And if you want to extract date1 and date2, try this:

Dante Amor
	A	B	C
2	D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:	12/08/2021	2021/06/12
3	D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12-08-2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:	12-08-2021	2021/06/12
4	D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:2021/06/12^F.T.:12-08-2021^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:	2021/06/12	12-08-2021
a

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH("/??/",SUBSTITUTE(A2,"-","/"))-5,14),":",REPT(" ",99)),"^",REPT(" ",99)),99,99))
C2:C4	C2	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH("/??/",SUBSTITUTE(A2,"-","/"),SEARCH(B2,A2)+10)-5,14),":",REPT(" ",99)),"^",REPT(" ",99)),99,99))

 

[Michael M]

You could also use a UDF
Copy this into a Standard Module

Function DA(s As String) As String 'extracts date based on fwd slash in date
With CreateObject("VBScript.RegExp")
  .Pattern = "(\d{1,2}\/\d{1,2}\/\d{4})" 'finds date in string eg, 21/03/2006
  If .Test(s) Then DA = .Execute(s)(0).SubMatches(0)
End With
End Function

Then use =DA(A2)

 

[Gokhan Aycan]

@Michael M There can be "-" as seperators as well, how do we modify the RegExp in that case?

Edit: Another question, what if seperator was not known ahead?

I really need to learn RegExp!

 

[Michael M]

Usin dashes as seperator

Function hyp(s As String) As String 'extracts date based on hyphen in date
With CreateObject("VBScript.RegExp")
  .Pattern = "(\d{1,2}\-\d{1,2}\-\d{4})" 'finds date in string eg, 21-03-2006
  If .Test(s) Then hyp = .Execute(s)(0).SubMatches(0)
End With
End Function

 

[Gokhan Aycan]

With some reading (VBA Regex - Automate Excel) and some testing, this looks to work with either / or - as seperator. Also changed the format to YYYY MM DD.

Function hyp(s As String) As String 'extracts date based on hyphen in date

    With CreateObject("VBScript.RegExp")
      .Pattern = "(\d{4}[-/]\d{1,2}[-/]\d{1,2})"
      If .test(s) Then hyp = .Execute(s)(0).SubMatches(0)
    End With

End Function

Immediate Window:
? hyp("sbshggs1920-12-12sndjsdnb")
1920-12-12

? hyp("sbshggs1920/12/12sndjsdnb")
1920/12/12

Edit: Answering my own question about seperator being unknown, this works:

.Pattern = "(\d{4}.\d{1,2}.\d{1,2})"

 

[Michael M]

There you go.....so you are now an expert Regex user.....

 

[Michael M]

@Rick Rothstein does it really well without Regex by using the LIKE operator.
I did have a copy but can't put my finger on it ATM.

 

[Rick Rothstein]

@Rick Rothstein does it really well without Regex by using the LIKE operator.

In order to be able to cope with 1 and 2 digit days and months, the Like operator solution would not be compact at all...

Function DA(ByVal S As String) As Date
  Dim X As Long
  S = Replace(Replace(S, "-", Application.International(xlDateSeparator)), "/", Application.International(xlDateSeparator))
  For X = 1 To Len(S)
    If Mid(S & "_", X) Like "####/##/##[!0-9]*" Then
      DA = CDate(Mid(S, X, 10))
      Exit For
    ElseIf Mid(S & "_", X) Like "####/#/##[!0-9]*" Then
      DA = CDate(Mid(S, X, 9))
      Exit For
    ElseIf Mid(S & "_", X) Like "####/##/#[!0-9]*" Then
      DA = CDate(Mid(S, X, 9))
      Exit For
    ElseIf Mid(S & "_", X) Like "####/#/#[!0-9]*" Then
      DA = CDate(Mid(S, X, 8))
      Exit For
    End If
  Next
End Function

 

[Peter_SSs]

As you can see there is a Date String (dd/mm/yyyy) embedded in this string. In this case the Date String is 12/08/2021 as it is shown in red below:

"D.:Virtual^Code-GF_SSO^N.H.:16^F.I.:12/08/2021^F.T.:2021/06/12^N.A.:1^N.I.:0^V.C.:32000^T.D.:O/C 12118^ID:"

That is not making sense to me since you highlighted the red date yet it is the blue one that seems to match.
Can you clarify exactly what it is that you are trying to do?

Perhaps you could give 4 or 5 varied other examples and the expected results so that we might better understand.

Using XL2BB would be very good because then we could see how your data and results are laid out and we could copy the data to test with.