Split data in one cell to multiple cells, and do the same in a lot of rows

[GlueGun]

Hi guys and girls,
I've looked in several forums but have not found anything that I understood and was able to rewrite so it suited my purposes.

The problem:
I have a time stamp that I received by a logging program and that time is the wrong format - I would like to have a relative time instead of exact.
Examples of time 13:37:34:819 (time is 1:37:34 pm)
I thought to solve it by split the time from one cell into multiple cells, and there is a function for it in Excel by selecting ":" as a separator.
The problem occurs when I want to do this for all 10,000 (or more) lines. I get it to work for a row, but I don't know so much macro programming so I get it to work on the rest of the rows.
The time should be on the X-axis in a graph so the time is essential for me.
I was going to do the time by splitting it up on colums and from there do the time relative from the first time stamp (by macro programming), but it would also be possible to start by the log file and remake the time before it gets into the Excel sheet. I thought this would be easy to do in Excel ...

My not working code, the aim is that it shall work on the whole sheet, but this is only written for 10 rows until it works:

Sub TimeSorting10rows()

   Range("A2").Select
   LastRow = Range("A65536").End(xlUp).Row
   For x = 1 To 10
     Selection.TextToColumns Destination:=Range("B2:B" & LastRow), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    ActiveCell.Offset(1, 0).Select
    Next

End Sub

A2 is containing 13:37:33:042 and the code makes B2, C2, D2 and E2 to get the content 13, 37, 33 and 042 but the Destination is not quite right, so every row goes to the "2"-row.
I have tried to replace the Range with something more suitable, but then I got compilation error instead, so Please Ladies and Gentlemen - give a novice a hint

 

[Bob Phillips]

Just reformat the column, there is an HH:mm:ss AM/PM format option

 

[p45cal]

The
:819
is 819 milliseconds right?

I couldn't get xld's suggestion to work (xl2010) but this seemed to (it overwrites the original values):

Sub TimeSortingMorerows()
LastRow = Range("A65536").End(xlUp).Row
With Range("A2:A" & LastRow)
  For Each cll In .Cells
    cll.Value = Left(cll.Value, 8) & "." & Right(cll.Value, 3)
  Next
  .NumberFormat = "h:mm:ss AM/PM"
End With
End Sub

 

[p45cal]

Ahh, relative time to the first cell; try this which will put the relative time in column B:

Sub TimeSortingMorerows()
LastRow = Range("A65536").End(xlUp).Row
With Range("A2:A" & LastRow)
  .NumberFormat = "h:mm:ss AM/PM"
  .Offset(, 1).FormulaR1C1 = "=RC[-1]-R2C1"
  For Each cll In .Cells
    cll.Value = Left(cll.Value, 8) & "." & Right(cll.Value, 3)
  Next
End With
End Sub

 

[GlueGun]

Thank you p45cal, case solved!

Your TimeSortingMorerows works with relative time for me. Now I can compare all of my graphs and check for when in time things happen in them! It was very complicated before because there are so many timestamps and things to check. :clap2: