Split Value

zezo2021

Member
Local time
Today, 01:17
Joined
Mar 25, 2021
Messages
412
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
 
Use the mid and instr functions something like

start time: trim(mid(mystring, 6, 12))
End time: trim(mid(mystring,instr(mystring,’to’)+4)))
 
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,
 
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
 
For a slightly more universal handling:
Code:
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
 

Users who are viewing this thread

Back
Top Bottom