Split Value

[zezo2021]

Hello friends

I import data from excel column
like this
From 7:00:00 AM To 7:59:59 AM


I want to split this value to 2 column

Start Time
7:00:00 AM
End Time
7:59:59 AM

 

[CJ_London]

Use the mid and instr functions something like

start time: trim(mid(mystring, 6, 12))
End time: trim(mid(mystring,instr(mystring,’to’)+4)))

 

[bastanu]

I would try to hook up to the key words ("From" and "To") as the lengths might vary :

StartTime: Trim(Replace(Left([SplitThis],InStr([SplitThis],"To")-1),"FROM",""))
EndTime: Trim(Mid([SplitThis],InStr([SplitThis],"To")+3))

Cheers,

 

[MajP]

As discussed earlier today, this works and if that is the exact format. Which you may have.
From 7:00:00 AM To 7:59:59 AM

If some are like this
7:00:00 AM - 7:59:59 AM
start 7:00:00 AM end 7:59:59 AM
or some other weird possibilities you can use RegExp to pull out all times no matter how many or where the appear in a string

Can an update query be used to pull date from longtext field?

I've got an odd one. I have a DB where there's a long text field and for each record, this long text field always starts with a date. I am trying to figure out a way to automate pulling this first date for each record and updating a separate field with it. I have thousands of records that are...

www.access-programmers.co.uk

 

[zezo2021]

Use the mid and instr functions something like

start time: trim(mid(mystring, 6, 12))
End time: trim(mid(mystring,instr(mystring,’to’)+4)))

Done
but change 4 to 3

 

[ebs17]

For a slightly more universal handling:

Public Function IsolateInformation(ByVal AnyText As String, ByVal SearchPattern As String) As Variant
' Examples for Pattern
' Date 1/12/2022    - "\d{1,2}/\d{1,2}/\d{4}"
' Time 7:59:59 AM   - "\d{1,2}:\d{2}:\d{2} [AP]M"
    
    Static oRegEx As Object
    Dim oMatchCollection As Object
    Dim lCount As Long
    Dim retArray() As Variant
    Dim i As Long

    If oRegEx Is Nothing Then Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
        .Pattern = SearchPattern
        .IgnoreCase = True
        .Global = True
        Set oMatchCollection = .Execute(AnyText)
        lCount = oMatchCollection.Count
        ReDim retArray(lCount)
        retArray(0) = lCount
        For i = 1 To lCount
            retArray(i) = oMatchCollection(i - 1)
        Next
        IsolateInformation = retArray
    End With
End Function

Sub evaluate_IsolateInformation()
    Const csText1 = "From 7:00:00 AM To 7:59:59 AM"
    Const csText2 = "From 1/1/2021 To 1/31/2021"
    Const csText3 = "Possible dates this week 1/2/2022 or 3/2/2022, or maybe next week on 8/2/2022"
    
    Dim myArray As Variant
    Dim i As Long
    
    myArray = IsolateInformation(csText3, "\d{1,2}/\d{1,2}/\d{4}")
    For i = 0 To UBound(myArray)
        Debug.Print myArray(i)
    Next
End Sub