How to parse and format a string into different cells RRS feed

  • Question

  • Dear all,

    I have the following CELL A which contains raw data as below :

    @@3000@213kg@15/04/2019 12:34:35<CR><LF>
    @@3001@214kg@15/04/2019 12:34:35<CR><LF>
    @@3000@215kg@15/04/2019 12:34:35<CR><LF>
    @@3004@217kg@15/04/2019 12:34:35<CR><LF>

    What I need to do is building a macro which take the content of my CELL A and split it into CELL B, C,D as below

    B             C           D                                   E                                     F
    0,213      kg        (datetime in seconds)   datetime in minutes)  15/04/2019 12:34:35
    1,214      kg        (datetime in seconds)   datetime in minutes)   15/04/2019 12:34:45
    0,215      kg        (datetime in seconds)   datetime in minutes)   15/04/2019 12:34:55
    4,217      kg        (datetime in seconds)   datetime in minutes)   15/04/2019 12:35:00

    The rule to create column B is based on the following :

    • The third @ from begining of string is replace with , and the digit before represent the unit
    • D column represent the F column in seconds
    • E column represent the F column in minutes

    I need to create a macro which parse this column A and build respecctive value in B, C,D, E F

    Any  idea how can I do this ? sorry never used VBA not familiar with

    Thanks for your help


    Wednesday, April 17, 2019 6:35 AM

All replies

  • Here is a macro for part of the solution. It doesn't fill columns D and E since I don't understand what you want there.

    Sub ParseData()
        Dim r As Long
        Dim m As Long
        Dim s As String
        Dim p1 As Long
        Dim p2 As Long
        Application.ScreenUpdating = False
        m = Range("A" & Rows.Count).End(xlUp).Row
        For r = 1 To m
            s = Range("A" & r).Value
            p1 = InStr(3, s, "@")
            Range("B" & r).Value = Mid(s, p1 - 1, 1) & "." & Val(Mid(s, p1 + 1))
            Range("C" & r).Value = "kg"
            p1 = InStr(p1 + 1, s, "@")
            p2 = InStr(p1 + 1, s, "<")
            Range("F" & r).Value = CDate(Mid(s, p1 + 1, p2 - p1 - 1))
        Next r
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (

    Wednesday, April 17, 2019 7:28 PM