To filter the un-necessary text from row and split date-CodePudding

I have tried my best but i could not get the desired result, below mentioned is the table in which i have to perform 2 functions

to filter out all text after "2022" because i need to filter out Actual file name and Date
After filtering the text i need to split Date with Actual file name, details are mentioned in picture

i have used left and right function to remove the text but somehow i could not be success,

Can anyone please help in this regard

CodePudding user response：

To use regular expressions in VBA you have to add "Microsoft VBScript Regular Expressions" to your project references.

Then you can use this user definded function:

Public Function extractDate(rg As Range) As Date

Dim strDate As String:

strDate = extractText(" (\d{1,2} .{3} \d{4}) ", rg.text)
extractDate = strDate

End Function


Private Function extractText(pattern As String, text As String) As String
 Dim regEx As RegExp: Set regEx = New RegExp
 Dim regEx_MatchCollection As MatchCollection
 Dim regEx_Match As Match
  
 regEx.pattern = pattern

 Set regEx_MatchCollection = regEx.Execute(text)
 If regEx_MatchCollection.Count = 0 Then
      extractText = vbNullString
 Else
    Set regEx_Match = regEx_MatchCollection(0)
    extractText = regEx_Match.SubMatches(0)
 End If

End Function

You then use the formula like this: