vba help - extract date from text file

[Mallesh23]

Hi Team,

How to extract dates (05May2021) from text file

FilePath = ThisWorkbook.Path & "\041 - Data.txt"

Constant word Reconcilation , date comes after that , date my varies.

--------Data------
Google 001 Reconcilation (offline) 05May2020 7:10 page 1 --------------- this lines is from Notepad tx.
Google 101 Reconcilation (offline) 05Jun2021 7:10 page 4


Thanks
mg

 

[Marc L]

Hi,​

​

you can use the Split VBA text function …​

 

[Mallesh23]

Hi Mark,
after splitting I need to check whether splitted value is date, how to check that



Thanks
mg

 

[Marc L]

​

Via IsDate VBA function …​

 

[Joe4]

Can you show us a little larger sample size, showing different variations in the data?
Will every record have the phrase "Reconciliation (offline)" before the date?

 

[Mallesh23]

Hi Joe,

Reconciliation (offline) is a standard word, always appear on first line of text file, No loop Required.
Google 001 Reconcilation (offline) 05May2020 7:10 page 1

What I am thinking
trim the string ,
split the text,
Check length = 9,
and check mid month Name (Jan-Dec)


Thanks
mg

 

[Joe4]

See if this gets you started.
This should pull the date portion of that string out of it.

Sub MySplit()

    Dim str As String
    Dim arr() As String
    Dim dte As String

'   Specify string to split
    str = Range("A1")

'   Split string
    arr = Split(str, " ")
   
'   Get value from 5th word
    dte = arr(4)
   
End Sub