Best approach to pull out a date from within a line of text?

peskywinnets · Apr 18, 2018

I have a text file that I'm reading into Access with VBA.

The first line of the text file will always follow this format )(though obviously the dates will change)...

Transaction Summary for 10 April 2018 to 18 April 2018

The to in that line will always be a constant (& the date I wish to extract will always follow on from it), so I seek a creative VBA way to capture all that follows the to in the line of text. Therefore I will end up with this...

18 April 2018

Which I can then trim & act upon to convert the text into a short date format (I know how to do this bit!)

What's the VBA function I can best deploy to trap the to & capture all the text that follows please?

Many thanks!

Gasman · Apr 18, 2018

Instr() function. I'd search for " to " to get the position
MID() or RIGHT() & LEN() to extract the portion you require?

HTH

isladogs · Apr 18, 2018

To build on Gasman's answer:

Code:

Dim strText As String, strDate As String
strText="Transaction Summary for 10 April 2018 to 18 April 2018"
strDate=Mid(strText,InStr(strText,"to")+3)

MarkK · Apr 18, 2018

Building on Colin's example, I would use Split()...

Code:

Sub Test193487091243()
    Dim tmp As String
    Dim d1 As Date
    
    tmp = "Transaction Summary for 10 April 2018 to 18 April 2018"
    d1 = Split(tmp, " to ")(1)
    
    Debug.Print d1
End Sub

Mark

Minty · Apr 18, 2018

Building on Mark's example,

I would use a pen and paper, and my circular slide rule

isladogs · Apr 18, 2018

Ha! LOL

Gasman · Apr 18, 2018

MarkK,
Could you please explain the (1)?
I can see what it does here, but unsure what else could be used?

TIA

MarkK said:
Building on Colin's example, I would use Split()...

Code:

Sub Test193487091243() Dim tmp As String Dim d1 As Date tmp = "Transaction Summary for 10 April 2018 to 18 April 2018" d1 = Split(tmp, " to ")(1) Debug.Print d1 End Sub

Mark

arnelgp · Apr 18, 2018

Dim strTemp as string
Dim myDate
StrTemp=Left(yourText, Instr(yourText, Chr(13))-1)
MyDate=CDate(split(strTemp, "To ")(1))

isladogs · Apr 18, 2018

As mark's offline, let me explain by comparing with use of Instr.
Both methods basically separate the string into 2 parts : Before/after 'to'
Spli ...(0) or left.. Instr both get the bit before the 'to'
Split....(1) or mid...Instr get the bit after the 'to'

I've been looking for meanings in the name of mark's sub but it probably just a random string &#55357;&#56841;

EDIT I see arnel has beaten me to it with a similar explanation

Gasman · Apr 18, 2018

Thank you Colin,

I could not see that in any Split function page I located.
However the code below produces Type mismatch on
tmpstring = Split(tmp, " to ")(1)

Code:

Sub Test193487091243()
    Dim tmp As String, tmpstring() As String
    Dim d1 As Date
    
    tmp = "Transaction Summary for 10 April 2018 to 18 October 2018"
    d1 = Split(tmp, "to ")(1)
    tmpstring = Split(tmp, " to ")(1)
    
    Debug.Print d1
    Debug.Print tmpstring(0)
    Debug.Print tmpstring(1)
    Debug.Print tmpstring(2)
    
End Sub

produces

ridders said:
As mark's offline, let me explain by comparing with use of Instr.
Both methods basically separate the string into 2 parts : Before/after 'to'
Spli ...(0) or left.. Instr both get the bit before the 'to'
Split....(1) or mid...Instr get the bit after the 'to'

I've been looking for meanings in the name of mark's sub but it probably just a random string ��

EDIT I see arnel has beaten me to it with a similar explanation

isladogs · Apr 18, 2018

Hi Gasman

The error was use of tmpString()
This does work

Code:

Sub Test193487091243xyzpsdq234b56as£()

    Dim tmp As String
    Dim dte1 As Date
    Dim strText0 As String, strText1 As String
    
    tmp = "Transaction Summary for 10 April 2018 to 18 October 2018"

    strText0 = Split(tmp, "to ")(0)
    strText1 = Split(tmp, "to ")(1)
    dte1 = Split(tmp, "to ")(1)
     
    Debug.Print "strText0 = " & strText0
    Debug.Print "strText1 = " & strText1
    Debug.Print "dte1 = " & dte1
    
End Sub

Output:
strText0 = Transaction Summary for 10 April 2018
strText1 = 18 October 2018
dte1 = 18/10/2018

MarkK · Apr 18, 2018

Gasman said:
Could you please explain the (1)?

Split returns an zero-based array. In the demonstrated problem, we are interested in returning the second element, so we use the subscript (1).
This code to me is overwrought...

Code:

Dim strTemp as string
Dim myDate
StrTemp=Left(yourText, Instr(yourText, Chr(13))-1)
MyDate=CDate(split(strTemp, "To ")(1))

If we use Split() we don't need Left() or Instr(). If we Dim myDate as date, then we also don't need CDate(), so we simplify to...

Code:

Dim myDate As Date
MyDate = Split(yourText, " To ")(1)

Colin, there is no significance to the Sub's name. I just write so many test routines, that I have a habit of typing in the next one as....
Sub Test1t02398471180643

Cheers all,
Mark

isladogs · Apr 18, 2018

MarkK said:
There is no significance to the Sub's name. I just write so many test routines, that I have a habit of typing in the next one as....
Sub Test1t02398471180643

Cheers all,
Mark

And I thought it was your phone number!!!!

P.S. I also think use of Split is neater than Mid ...InStr for this example

peskywinnets · Apr 18, 2018

Thanks to all who contributed...some great ideas, in the end I've gone with Markk's code (I'd forgot about the split function ...even though I've used it elsewhere in the past couple of weeks - doh) ...Mark's code works a treat

arnelgp · Apr 18, 2018

I know about split

Quote:
Originally Posted by Gasman View Post
Could you please explain the (1)?
Split returns an zero-based array. In the demonstrated problem, we are interested in returning the second element, so we use the subscript (1).
This code to me is overwrought...
Code:
Dim strTemp as string
Dim myDate
StrTemp=Left(yourText, Instr(yourText, Chr(13))-1)
MyDate=CDate(split(strTemp, "To ")(1))
If we use Split() we don't need Left() or Instr().

The OP has a text file not a single line of text.
The text we parse is but the first line.
There are many lines that may follow. See post #1.

peskywinnets · Apr 18, 2018

arnelgp said:
The OP has a text file not a single line of text.

Correct, but I always read the first line of the text file into a string (as a pre-cursor to handling the overall text file separately), therefore to all intents & purposes I needed a solution for pulling date info out of a single string.

peskywinnets · Apr 18, 2018

This code....

Code:

Dim strTemp As String
Dim myDate
strTemp = Left(strContent, InStr(strContent, Chr(13)) - 1)
SettlementDate = CDate(Split(strTemp, "To ")(1))

...worked for on the string I provided in my opening post, i.e....

Transaction Summary for 10 April 2018 to 18 April 2018

However on this string...

"Transaction Summary for 13 February 2018 to 27 February 2018"

I get the following error...

Run-time Error '9':

Subscript out of range

I've been at the screen all day...I've got brain fuzz...what am I missing?!!

isladogs · Apr 18, 2018

It works for me using the simpler version of the code by MarkK as below

Either use Instr OR use Split - you DON'T need both

peskywinnets · Apr 18, 2018

Thanks.

As you say, it only needs trim...

Code:

Dim strContent As String
Dim SettlementDate As Date

    strContent = "Transaction Summary for 10 April 2018 to 18 April 2018"
    SettlementDate = Split(strContent, " to ")(1)
    
    Debug.Print SettlementDate

the above works

Best approach to pull out a date from within a line of text?

Registered User.

Enthusiastic Amateur

MVP / VIP

bit cruncher

AWF VIP

MVP / VIP

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

MVP / VIP

Enthusiastic Amateur

MVP / VIP

bit cruncher

MVP / VIP

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

MVP / VIP

Attachments

Registered User.

Similar threads

Users who are viewing this thread