Best string Function (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
Hi
I have a text field as the below and i need to convert to date.
How can I tell VBA to remove all data after third space?



May 4 2018 12:47:00 CST ECT
December 10 2018 18:24:00 CST
December 10 2018 18:24:00 CST
December 10 2018 18:24:00 CST
November 30 2018 13:15:00 CST
November 30 2018 13:15:00 CST
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:58
Joined
May 7, 2009
Messages
19,175
CDate(left(theString,instr(theString,"2018")+len("2018")-1)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
Hi. Quick question. Would all the data always have 00 seconds?
 

isladogs

MVP / VIP
Local time
Today, 13:58
Joined
Jan 14, 2017
Messages
18,186
This should work
Left(FieldName, Instr(FieldName,":")-3)
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
One problem is that "2018" might be 2019 and in few month 2020.
So, could we replace somehow the string 2018 ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:58
Joined
May 7, 2009
Messages
19,175
or use RegExpr to return any date (any year).

CDate(DatesInText(theString))

Code:
'arnelgp
'find date inside a string
'
'returns string of dates if found
Public Function DatesInText(strText As String) As String
    'Dim arrPattern(1 To 36) As String
    Dim arrPattern(1 To 9) As String
    Dim oRE, oMatches, oMatch
    Dim i As Integer
    Dim strDates As String
    Dim collDates As Collection
    
    'month-day-year
    arrPattern(1) = "([1-9]|0[1-9]|1[012])[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])[- /.]((19|20)[0-9]{2}|[0-9]{2})"
    arrPattern(2) = "(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])[- /.]((19|20)[0-9]{2}|[0-9]{2})"
    arrPattern(3) = "(january|february|march|april|july|august|september|october|november|december)[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])[- /.]((19|20)[0-9]{2}|[0-9]{2})"
    'day-month-year
    arrPattern(4) = "([1-9]|0[1-9]|[12][0-9]|3[01])[- /.]([1-9]|0[1-9]|1[012])[- /.]((19|20)[0-9]{2}|[0-9]{2})"
    arrPattern(5) = "([1-9]|0[1-9]|[12][0-9]|3[01])[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]((19|20)[0-9]{2}|[0-9]{2})"
    arrPattern(6) = "([1-9]|0[1-9]|[12][0-9]|3[01])[- /.](january|february|march|april|july|august|september|october|november|december)[- /.](((19|20)[0-9]{2})|[0-9]{2})"
    'year-month-day
    arrPattern(7) = "((19|20)[0-9]{2}|[0-9]{2})[- /.]([1-9]|0[1-9]|1[012])[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])"
    arrPattern(8) = "((19|20)[0-9]{2}|[0-9]{2})[- /.](jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])"
    arrPattern(9) = "((19|20)[0-9]{2}|[0-9]{2})[- /.](january|february|march|april|july|august|september|october|november|december)[- /.]([1-9]|0[1-9]|[12][0-9]|3[01])"
    
    Set oRE = CreateObject("VBScript.RegExp")
    Set collDates = New Collection
    With oRE
        .Global = True
        .IgnoreCase = True
        On Error Resume Next
        For i = 1 To 9
            .Pattern = arrPattern(i)
            Set oMatches = .Execute(strText)
            For Each oMatch In oMatches
                collDates.Add oMatch.value, oMatch.value
                'Debug.Print oMatch.value
            Next
        Next
    End With
    'remove dates not in origial text
    For i = collDates.Count To 1 Step -1
        With oRE
            .Pattern = "(^|[ \x0A\,\.])" & collDates(i) & "($|[ \x0A\,\.])"
            Set oMatches = .Execute(strText)
            If oMatches.Count = 0 Then _
                collDates.Remove collDates(i)
        End With
    Next
    For i = 1 To collDates.Count
        strDates = strDates & collDates(i) & ","
    Next
    Set collDates = Nothing
    Set oMatches = Nothing
    Set oRE = Nothing
    If strDates <> "" Then strDates = Left(strDates, Len(strDates) - 1)
    DatesInText = strDates
End Function
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
The -3 doesn't work because in some records would need -7


Yes, seconds most likely will always be 00
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
The -3 doesn't work because in some records would need -7

Yes, seconds most likely will always be 00
Only if it's always going to be 00, then you could also try the following:
Code:
DateValue(CDate(Left("May 4 2018 12:47:00 CST ECT",InStrRev("May 4 2018 12:47:00 CST ECT","0"))))
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:58
Joined
Feb 19, 2013
Messages
16,555
if this is in vba then use the split function

dValue=cDate(split(mystring," ")(0) & " " & split(mystring," ")(1) & " " & split(mystring," ")(2))
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
Really? Can you show me an example that would be -7?
All of the examples supplied would work I believe


December 10 2018 18:24:00 CST
December 10 2018 18:24:00 CST EST


Yes, because some records have more than three letters on the right end of string.
Like the above example.
 

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
December 10 2018 18:24:00 CST
December 10 2018 18:24:00 CST EST


Yes, because some records have more than three letters on the right end of string.
Like the above example.

No matter how many characters you have at the right side, still isladogs' code works. because he's reading the left side of the first occurrence of : in your date string.

Code:
   mystring = "December 10 2018 18:24:00 CST EST"
   Debug.Print Left(mystring, InStr(mystring, ":") - 3)

   mystring = "December 10 2018 18:24:00 CST"
   Debug.Print Left(mystring, InStr(mystring, ":") - 3)

In both cases you will have -> December 10 2018
 
Last edited:

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
if this is in vba then use the split function

dValue=cDate(split(mystring," ")(0) & " " & split(mystring," ")(1) & " " & split(mystring," ")(2))


Hi CJ_London
I get the below error message


The expression you entered has an invalid.(dot) or ! operator or invalid parentheses.
You may have entered an invakid identifier or typed parentheses following the Null constant.
 

isladogs

MVP / VIP
Local time
Today, 13:58
Joined
Jan 14, 2017
Messages
18,186
Thanks Tera
Leo Polla - you could at least have tried my method before wrongly saying it doesn't work
 
Last edited:

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
Hi CJ_London
I get the below error message


The expression you entered has an invalid.(dot) or ! operator or invalid parentheses.
You may have entered an invakid identifier or typed parentheses following the Null constant.

CJ_London's has no error. You may have mistype it. Check it again.
You have to substitute mystring with your field name.
If you still receive the error, can you post your code?

CJ_London's code returns a date formatting with your control panel's setting (yyyy/mm/dd or something like this) which is different with your request in the first post.

How can I tell VBA to remove all data after third space?
 
Last edited:

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
Thanks Tera
Leo Polla - you could at least have tried my method before wrongly saying it doesn't work


Hi isladogs, apologies for being so perfunctory, you code works just fine.

I took it wrong that "3" is the number of letters we have to remove from right side.
 

isladogs

MVP / VIP
Local time
Today, 13:58
Joined
Jan 14, 2017
Messages
18,186
No. As you probably now realise its the number of characters to remove before the first ":"
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:58
Joined
Mar 24, 2014
Messages
364
Hi, I have mastered the first "date" string problem. Thanks to everyone

Now, i want from a field to use the MID function.

Start from the 9th character(from lest to right) and stop at the first double space.
Example the field is "123456789abc defg hzzzzzzz" return "ab cdefg". Note, between g and h are 2 spaces.


I tried to combine MID and InStr function but no success
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
Hi, I have mastered the first "date" string problem. Thanks to everyone

Now, i want from a field to use the MID function.

Start from the 9th character(from lest to right) and stop at the first double space.
Example the field is "123456789abc defg hzzzzzzz" return "ab cdefg". Note, between g and h are 2 spaces.


I tried to combine MID and InStr function but no success
Hi. Would there ever be a third space or another space after "hzzzzzz"?
 

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
You have too many mistakes in your question and it makes it hard to answer

Start from the 9th character ....... return "ab cdefg".
9th character is 9 not a. a is the tenth character

....... return "ab cdefg".
There is no space between b and c in your string

for now is it what you want?

str = "123456789abc defg hzzzzzzz"
Debug.Print Mid(str, 10, Len(str) - InStr(str, " "))
' there is two spaces between quotation marks
 

Users who are viewing this thread

Top Bottom