none
How to parse a string into different cells RRS feed

  • Question

  • Dear all,

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

    @@3020@213kg@15/04/2019 12:34:35<CR><LF>
    @@3021@214kg@15/04/2019 12:34:35<CR><LF>
    @@3022@215kg@15/04/2019 12:34:35<CR><LF>
    @@3023@216kg@15/04/2019 12:34:35<CR><LF>
    @@3024@217kg@15/04/2019 12:34:35<CR><LF>

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

    B          C           D
    3020    213kg    15/04/2019 12:34:35
    3021    214kg    15/04/2019 12:34:35
    3022    215kg    15/04/2019 12:34:35
    3023    216kg    15/04/2019 12:34:35
    3024    217kg    15/04/2019 12:34:35

    Can anyone help on how to split it in vba ?

    regards

    Monday, April 15, 2019 6:26 AM

Answers

  • Create the code as follows.
    1. Ensure that you don't have any other data out to right of your data
    2. Turn on the Macro Recorder
    3. Select the column with the data
    4. Select Data ribbon
    5. Select Text to columns
    6. In the wizard select "Delimited" (should have defaulted to that option)
    7. Click Next
    8. Under Delimiters check "Other" (all other boxes unchecked)
    9. In the field beside "Other" insert @
    10. Click Next
    11. Click Finish
    12. You will then have 3 columns but last column still has the <CR><LF>
    13. Repeat steps 3 - 11 above but on the 3rd column and step 9 set the delimiter to  <
    14. Delete the unwanted columns to the right.
    15. Turn off the macro Recorder

    You should have code something like the following.

    Sub Macro1()
        Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, 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), Array(5, _
            1)), TrailingMinusNumbers:=True
       
        Columns("E:E").Select
        Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="<", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
            TrailingMinusNumbers:=True
        Columns("F:G").Select
        Selection.Delete Shift:=xlToLeft
    End Sub


    Regards, OssieMac

    • Marked as answer by wakefun Monday, April 15, 2019 1:15 PM
    Monday, April 15, 2019 11:32 AM