Trying to modify this 'text to rows' code to only use semicolons as delimiters

  • Hello All,


    What I've been trying to do is reverse engineer this code that a previous guru of this board posted to only use semicolons as delimiters.


    I currently have data in single cells like this 'John Smith;Ed Jones;Bob Thomas', but I want to separate the names into individual rows, copying and inserting the data from all of the rows like this current code does.


    I believe I can omit most of the bottom half since I'm only dealing with one delimiter, not parenthesis and brackets like this macro does.


    Please let me know if you need any other information.



    Thanks!
    Mike

  • Re: Trying to modify this 'text to rows' code to only use semicolons as delimiters


    Can you supply a sample workbook with no sensitive data - what you are asking sounds very straight forward but I am not sure how your data is set out. As a really simple example


    See attached workbook

    Files

    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: Trying to modify this 'text to rows' code to only use semicolons as delimiters


    Code
    Sub M_snb()
       sn = Split(Join(Application.Transpose(Columns(1).SpecialCells(2)), ";"), ";")
       Cells(1, 4).Resize(UBound(sn) + 1) = Application.Transpose(sn)
    End Sub

Participate now!

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