How to use split in VBA for time

Question

I'm very new to VBA, and for a macro I'm working on, I'm trying to split out strings of the form:

"Duration: __ Minutes __ Seconds"

I am trying to get the total time in minutes from this. However, if the time is less than a minute then it would look like

"Duration: __ Seconds"

My question is, how would I use the Split function to cover both of these cases? No need to even use split if that's easier, thanks!

So for example, if I had the string "Duration: 6 Minutes 30 Seconds", I expect the result 6.5 and if for the string "Duration: 45 Seconds" I expect 0.75.

What form? What does your input data look like? What kind of result do you expect? — MonkeyZeus, Jul 23 '19 at 17:37
The strings will either look like "Duration: 06 Minutes 30 Seconds" or "Duration: 45 Seconds", and the result that I expect is 6.5 and 0.75 respectively — Vincent Le, Jul 23 '19 at 17:46
You should edit your question to clarify that because there was absolutely ZERO chance of me guessing that based on the post itself and I am sure that this applies for anyone else that has come across this post. — MonkeyZeus, Jul 23 '19 at 17:47

Matteo NNZ · Accepted Answer · 2019-07-23T17:57:23.823

Define a user function:

Public Function getMinutes(ByVal input As String) As Double
    Elements = Split(input, " ") 'split the string by space
    If InStr(input, "Minutes") > 0 Then 'if the string contains the word minutes
       mins = Elements(1) 'the element at index 1 is the minutes
       secs = Elements(3) 'the element at index 3 is the seconds
    Else 'if not
       secs = Elements(1) 'just the element at index 1 is the seconds
    End If
    getMinutes = mins + secs/60 'return the minutes as they are (they may be zero) and the seconds divided by 60
End Function

Use it like this:

SampleInput1 = "Duration: 06 Minutes 30 Seconds"
myMinutes = getMinutes(SampleInput1) 'output => 6.5

SampleInput2 = "Duration: 45 Seconds"
myMinutes = getMinutes(SampleInput2) 'output => 0.75

You may want to test further the input against a regex expression to make sure it has the correct form before performing the operations. Check this brilliant answer to know how to test a string for a pattern using regex in VBA. Your patterns would be:

Duration: \d+ Minutes \d+ Seconds
Duration: \d+ Seconds

How to use split in VBA for time

1 Answers1