+ Reply to Thread
Results 1 to 22 of 22

split multiple semicolon separated values into new rows for multiple columns

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    split multiple semicolon separated values into new rows for multiple columns

    Hello,

    I currently have data that reads like the attached. I've gotten quite far cobbling some code together but for the life of me I just can't figure out this last moderation. Can somebody help adjust my code so that the semicolon separated values are split in columns B & C simultaneously and create corresponding rows below with the original rows data in the remaining columns (A, D, E, F, G) ?

    The workbook attached contains three tabs. Raw is the raw datam Current Output is the result of my current macro which is also included in the workbook, and Desired Output is what I want it to look like after the macro. Any help would be greatly appreciated, thank you!


    Code:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by FDibbins; 01-02-2014 at 06:21 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: split multiple semicolon separated values into new rows for multiple columns

    Hi and welcome to the forum

    Please note - Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I will add them for you - this time
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Talking Re: split multiple semicolon separated values into new rows for multiple columns

    thanks! apologies first time posting. Again I appreciate any help that anyone can offer on this one because it's really stumped me.


    Thanks again in advance

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: split multiple semicolon separated values into new rows for multiple columns


    Hi,

    any mistake in row #2 / Col 3 of the Desired Output ?

  5. #5
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    Hi,

    Thanks for the response, yes, that cell should simply read "WATER".

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: split multiple semicolon separated values into new rows for multiple columns


    Split rows into same original worksheet or another worksheet ?

  7. #7
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    original worksheet

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this :

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    works excellent thank you!!!

  10. #10
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    apologies I am getting a subscript out of range error when I run the code. Since I have some cases where column 2 will be blank and column 3 populated and vice versa (in any case at least one of those columns will always be populated) I suppose this might be the cause of the error. Is there a quick line or two that might account for this case?

    This is the line where I am getting the error:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: split multiple semicolon separated values into new rows for multiple columns


    Can't see, I need a workbook occuring this issue …
    Last edited by Marc L; 01-03-2014 at 11:31 AM. Reason: see next post …

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Try this :

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    Code runs without any errors now but I am noticing the only thing it seems to be missing is the case where this is a value in column 3 but not in column 2. In this case the values appear to be missing and are not present in the output. Can we make it so that it still outputs those lines as well? Thanks in advance.

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: split multiple semicolon separated values into new rows for multiple columns


    OK but I need a workwook with original worksheet with all possibles cases and another worksheet
    as Desired result … Hope to avoid a gas factory code !

  15. #15
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    cobble-2.xlsm attached! Thank you!

  16. #16
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hope you don't forget any case, try this !

    Please Login or Register  to view this content.
    Hope my reputation will increase …
    Last edited by Marc L; 01-03-2014 at 03:46 PM.

  17. #17
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    Thank you again, I know this must be a pain, but now I'm getting a type mismatch error at the very beginning on the below line:

    Please Login or Register  to view this content.

  18. #18
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: split multiple semicolon separated values into new rows for multiple columns


    I see nothing in this line that can occurs this error, no such error within your last workbook !

    And this line is the same than in previous code … Maybe restart Excel, the computer …

    Without the same worksheet data …

  19. #19
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    i restarted excel and now it works, ha! thank you!!

  20. #20
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: split multiple semicolon separated values into new rows for multiple columns

    Please Login or Register  to view this content.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: split multiple semicolon separated values into new rows for multiple columns

    Try
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    01-02-2014
    Location
    Tri-State Area, USA
    MS-Off Ver
    Excel 2010
    Posts
    26

    Re: split multiple semicolon separated values into new rows for multiple columns

    Thank you so much for the help, I am able to execute the data as needed! Thank you all again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Split multiple values in a cell to multiple rows
    By karmithr in forum Excel General
    Replies: 2
    Last Post: 03-04-2013, 08:34 PM
  2. Import multiple semicolon separated csv files
    By Ivodv in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-07-2012, 08:17 AM
  3. CSV file with two columns multiple values separated by pipe. Need to Combine
    By nikythebest in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-01-2012, 04:30 AM
  4. Best way to parse colums and rows w/ multiple values separated by comma?
    By Sutukh19 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-28-2009, 04:37 AM
  5. Column with semicolon-separated values
    By nsv in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2006, 08:22 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1