Very complex IF syntax needed

[fdv]

Hello all! I am new to Excel and have been charged with analyzing some call times for the local ambulance squad. Number of minutes elapsed between dispatch, their answering, their responding to the scene, time spent on scene, etc.

I have a spreadsheet with two fields, date and one that contains all of the crucial data, like this:

10:00:009:53:5810:13:039:48:58201/3/2010
9:23:579:38:449:19:503410/22/2010

I am pretty certain that, based on the location of the colons, Excel can put a comma between times so that I can separate these values into their own fields, right? The above examples ideally should be transformed into:

10:00:00,9:53:58,10:13:03,9:48:58,201/3/2010
9:23:57,9:38:44,9:19:50,3410/22/2010

Everything after the two digits after the very last colon can go away, I don't care about it. In other words, in my examples, 201/3/2010 and 3410/22/2010 will be scrapped, I already have the date separately. I just need to parse left to right and insert commas after the pattern :##:## (colon two digits colon two digits).

What should I do?

Thanks in advance

 

[xc405]

Im a little confused with what you need; what do you need the IF stmt to do?
You can send me your excel sheet and I'll get the times right, you can use a number format in the cell format box to format the number you enter as times

 

[Marcelo Branco]

Hi,

Welcome to the forum!

Maybe this UDF (User Defined Function)

A B
<TABLE style="WIDTH: 400pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=534><COLGROUP><COL style="WIDTH: 236pt; mso-width-source: userset; mso-width-alt: 11520" width=315><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 8009" width=219><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 236pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20 width=315>10:00:009:53:5810:13:039:48:58201/3/2010


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 164pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=219>10:00:00, 9:53:58, 10:13:03, 9:48:58</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>9:23:579:38:449:19:503410/22/2010


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2775758 class=xl65>9:23:57, 9:38:44, 9:19:50</TD></TR></TBODY></TABLE>

Function InsertComma(t As String)
    Dim RegEx As Object, RegMatches As Object
    Dim RstStr As String, i As Long
 
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = True
        .Pattern = "(\d{1,2})\:(\d{2})\:(\d{2})"
        Set RegMatches = .Execute(t)
    End With
 
    For i = 0 To RegMatches.Count - 1
        RstStr = RstStr & RegMatches(i) & ", "
    Next i
 
    InsertComma = Left(RstStr, Len(RstStr) - 2)
End Function

Copy (Ctrl+C) this code
Alt+F11 to open the VBEditor
Insert > Module
Paste (Ctrl+V) the code in the right-panel

Usage
in B1 enter
= InsertComma(A1)

copy down

HTH

M.

 

[Marcelo Branco]

Forgot to say: save the file as macro enabled

M.

 

[Biz]

I am try to learn how to create UDF and this was looks very good.

 

[Marcelo Branco]

I am try to learn how to create UDF and this was looks very good.

Hi Biz,

Did you try it and it worked?

M.

 

[fdv]

Wow Marcelo you nailed it.
I was expecting some IF statement looking for colons in the string. This works awesomely well.
Thanks to everyone who replied! Cheers!

 

[Marcelo Branco]

Wow Marcelo you nailed it.
I was expecting some IF statement looking for colons in the string. This works awesomely well.
Thanks to everyone who replied! Cheers!

Tks for the feedback! Glad to help

M.

 

[Biz]

Hi Biz,

Did you try it and it worked?

M.

It works mate like Superman!

Biz