Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    Code:
    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
    Last edited by NBVC; 2014-08-07 at 04:07 PM.

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

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,478
    Articles
    0
    Excel Version
    365
    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):
    Code:
    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

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,478
    Articles
    0
    Excel Version
    365
    Ahh, relative time to the first cell; try this which will put the relative time in column B:
    Code:
    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

  5. #5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •