CODEWITHSUNDEEP
regexvbastringsplit
Vincent Le
Vincent Le

Reputation: 5

How to use split in VBA for time

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.

Upvotes: 0

Views: 71

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12685

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

Upvotes: 2

Related Questions