Macro to delimit and create column headers

  • Hi All


    I have a column with rows having strings as below (see forum.ozgrid.com/index.php?attachment/58397/excel attached) .


    I want to delimit the string in such a way that the for each parameter, the parameters will beome columns with headers and the value against them will be shown in thier respective column. The output tab in the attached excel might make what I am trying to convey more clear.


    Sp bascically CPU should have its own column and its time against it.


    There are thousands of such column that we wish to analyse and therefore the ask.


    I tried various option to delimit including recording a macro but did not work :(


    Hope someone ca help me or direct me here.


    Thanks!
    [TABLE="width: 208"]

    [tr]


    [td]


    String

    [/td]


    [/tr]


    [tr]


    [td]

    CPU: 5,622.871 ms, Sync: 0 ms, Wait: 0 ms, Suspension: 2,399
    .921 ms

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Macro to delimit and create column headers


    Will each string have the same format - in your example they all seem to CPU Sync Wait Suspension ? So in essence the first string will determine the columns then the remainder of the strings you only need to get the data.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Macro to delimit and create column headers


    The easiest way to do this would be with regular expressions. Will there only just be the 4 headers? You just want the data extracted? On my way home. Will post solution when I get home if someone else not picked thread up.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Macro to delimit and create column headers


    Thanks.


    You are correct on both counts. Yes there will be only four headers always.


    And I simply want to extract data as aligned in the output tab.

  • Re: Macro to delimit and create column headers


    Hi..


    Assuming you have already taken care of those commas (at start and end of string).. this should do it..



    Check the attachment to see it working..


  • Re: Macro to delimit and create column headers


    Thanks apo!!



    Checked and worked great.


    Is it possible to remove the "ms" from the result and divide all numbers by 1000?


    Thanks.

  • Re: Macro to delimit and create column headers


    Hi..


    You could just do a find and replace on your raw data to remove the "ms"..


    Just below this line:

    Code
    With Sheets("Input").Range("A1").CurrentRegion


    add this...

    Code
    .Replace What:="ms", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False


    As for dividing by 1000.. if no one chimes in.. I will take a look at that later or tomorrow.. (missus wants to watch a movie right now)...


    Cheers

  • Re: Macro to delimit and create column headers


    or


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!