Pull number of hours from a string

[kizzie37]

I have some strings of text in a report showing scheduling (some examples)

M 9a-5p RF 830a-1p (i.e. monday 9an to 5pm and Thursday & Friday 930am to 1pm)
M 9a-5p T 915a-4p W 830a-4p R 1120a-705p F 930a-4p
M 9a-5p TWRF 9a-1p
M 9a-5p WRF 830a-1p

I dont need to know the number of "days" but I do want to pull our the number of hours between each "time" listed in the string. the results can appear in multiple columns. So for this one for example M 9a-5p T 915a-4p W 830a-4p R 1120a-705p F 930a-4p I would end up with 5 different "Day Lengths" that could go into 5 separate columns.

I hope this makes sense?

 

[lrobbo314]

Using VBA.

Book1
	A	B
1	M 9a-5p RF 830a-1p	8, 4.5
2	M 9a-5p T 915a-4p W 830a-4p R 1120a-705p F 930a-4p	8, 6.75, 7.5, 7.75, 6.5
3	M 9a-5p TWRF 9a-1p	8, 4
4	M 9a-5p WRF 830a-1p	8, 4.5
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=xTime(A1)

Function xTime(s As String)
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim d1 As Date, d2 As Date
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+[ap]"
    .Global = True
    Set matches = .Execute(s)
    For i = 0 To matches.Count - 1 Step 2
        If Len(matches(i)) > 2 Then
            d1 = Application.WorksheetFunction.Replace(matches(i), Len(matches(i)) - 2, 0, ":")
        Else
            d1 = matches(i)
        End If
        If Len(matches(i + 1)) > 2 Then
            d2 = Application.WorksheetFunction.Replace(matches(i + 1), Len(matches(i + 1)) - 2, 0, ":")
        Else
            d2 = matches(i + 1)
        End If
        AL.Add (d2 - d1) * 24
    Next i
End With

xTime = Join(AL.toarray, ", ")
End Function