How can I split cells with complex values into date and time in VBA?

Question

VBA Split Cell w/ Complicated value

I am attempting to split cells with values as the following

I would like to separate it by date, and time. I've attempted to use split on a for loop, I've even attempted to macro out a text to column delimiter, and nothing is coming out as I wish. As long as I am able to get the time, that is all that matters. Any help, or even a push in the right direction would be appreciated

Should be doable with [regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). — BigBen, May 18 '23 at 18:28
Thank you! This seems like it'll work for me! I'll look into it, and I'll inform later! — Nytro1987, May 18 '23 at 18:38

score 1 · Answer 1 · answered May 18 '23 at 20:22

Following the structure of you example string you list pairs of stopped times within a day where "--" combines all start-end values, while "," separates each pair.
Assuming the same day date for any pair of listed start-end times, you could try the following:

a) get the identical day date (dayPrefix) by a first split action and define this string as split delimiter
b) split the slightly modified input string s into time tokens
c) remove the starting split element as it's automatically empty
d) return a zero-based array of all time tokens

Function GetAllTimeTokens(ByVal s As String)
'Purp: isolate all time tokens (within pairs of date time list)
'Note: assumes same day date for all time indications
    Const DEL = "$DEL$"
'a) get day prefix
    Dim dayPrefix As String
    dayPrefix = Split(s, " ")(0)
'b) split into time parts
    s = Replace(Replace(s, "--", ""), ",", "")
    Dim tmp As Variant
    tmp = Split(s, dayPrefix & " ")
'c) remove starting split element (being empty)
    tmp(0) = DEL: tmp = Filter(tmp, DEL, False)
'd) return function result
    GetAllTimeTokens = tmp
End Function

Example call

Sub ExampleCall()
    Dim s As String
    s = "2023-05-16 06:55--2023-05-16 07:05,2023-05-16 08:45--2023-05-16 09:15,2023-05-16 11:00--2023-05-16 11:10"

    Dim arr As Variant
    arr = GetAllTimeTokens(s)        ' << returns zero-based 1D array of paired time tokens
    
    'Output in VB Editor's immediate windo
    Debug.Print "~~> " & Join(arr, "|")
    '~~> 06:55|07:05|08:45|09:15|11:00|11:10

End Sub

Use the resulting 0-based 1D array arr following your requirements, where e.g. index pair 0..1 would indicate arr(0) as first start time 06:55, while arr(1) would display the following end time 07:05, etc. for all even/odd index pairs.

score 0 · Answer 2 · answered May 19 '23 at 13:02

RegEx is certainly a good way to solve the problem. You had the right idea with your original approach so more for your benefit than anything else, you could have done this:

Private Sub GetTimes(ByVal ComplexValue As String)
   Dim Entries As Variant
   Dim Entry As Variant
   Dim DateAndTimes As Variant
   Dim DateAndTime As Variant
   Dim Time As Variant
   
   Entries = Split(ComplexValue, ",")
   
   For Each Entry In Entries
      DateAndTimes = Split(Entry, "--")
      
      For Each DateAndTime In DateAndTimes
         Time = Split(DateAndTime, " ")(1)
         Debug.Print Time
      Next
   Next
End Sub

How can I split cells with complex values into date and time in VBA?

2 Answers2