As the flavor was not specified, Excel VBA was used, which uses javascript .
Regex
Enable Regex in Excel
RegEx needs to be enabled, Enable Developer mode
In the 'Developer' tab, click 'Visual Basic' and the VBA window will open.
Go to 'Tools' - > 'References ...' and a window will open.
Look for 'Microsoft VBScript Regular Expressions 5.5', as in the image below. And enable this option.
Standard
AsimpleRegexthatidentifiesifithasnumbers:
Demo on Regex101 and the default: \d{2}\/\d{2}\/\d{4}
Excel VBA code
Validate Data
This pattern only takes a string in the format 2number / 2number / 4number and the validation of the Date to verify that it is valid is performed by Excel with IsDate() .
IsDate can cause some errors (because it tries to check if it's date in universal formats, from several countries ...), refer to this answer for another way to check if it is date.
Sub ValidarDados()
Dim objStream As Object
Dim strData As String
Dim fileName As String, textData As String, fileNo As Integer
sFilename = "temp.txt"
sFilepath = ThisWorkbook.Path & "\" & sFilename
fileNo = FreeFile 'Get first free file number
Inicio:
If Dir(sFilepath) <> "" Then
Open sFilepath For Input As #fileNo
strData = Input$(LOF(fileNo), fileNo)
Dim objMatches As Object, objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")
'Regex
objRegExp.Pattern = "\d{2}\/\d{2}\/\d{4}"
objRegExp.Global = True
Set objMatches = objRegExp.Execute(strData)
If objMatches.Count <> 0 Then
For Each m In objMatches
If IsDate(m) Then MsgBox m 'Debug.Print m
Next m
End If
Else
MsgBox "O arquivo não pôde ser carregado - Escolha o arquivo."
strPath = EscolherCaminho
GoTo Inicio
End If
'Close
Close #fileNo
End Sub
Choose Path
If you do not find the path mentioned in the code, a window will open to choose the file in the system.
Public Function EscolherCaminho() As String
'Créditos: http://software-solutions-online.com/excel-vba-open-file-dialog/
Dim intChoice As Long
Dim strPath As String
'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
'print the file path to sheet 1
EscolherCaminho = strPath
End If
End Function
Results
With this data found in a test in txt:
- 04/20/2017
- 04/20/2017
- 06/21/2016
- 04/20/2017
- 04/20/2017
- 04/31/2017
- 02/30/2017
- 20/13/2017
After validation with IsDate() , these are the results:
- 04/20/2017
- 04/20/2017
- 06/21/2016
- 04/20/2017
- 04/20/2017
