Results 1 to 2 of 2

Thread: Move everything after comma to new row

  1. #1
    Seeker Bine's Avatar
    Join Date
    Dec 2020
    Posts
    9
    Articles
    0
    Excel Version
    Excel 2016

    Move everything after comma to new row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear all,

    I have a list of lots of genes and unfortunately sometimes I have more than one gene in one row e.g.:

    ENSG00000063978, ENSG00000125386
    ENSG00000248323
    ENSG00000137261
    ENSG00000235220, ENSG00000204642, ENSG00000137403, ENSG00000237508, ENSG00000229698, ENSG00000206509
    ENSG00000183826
    ENSG00000135298
    ENSG00000185345
    ENSG00000106066
    ENSG00000283239, ENSG00000274726, ENSG00000104728
    ENSG00000153707


    But I want that every gene is in a separate row without comma, like this (first 4 lines of above example):


    ENSG00000063978
    ENSG00000125386
    ENSG00000248323
    ENSG00000137261

    Has anyone an idea?

    Thank you so much!!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,023
    Articles
    0
    Excel Version
    365
    This is straightforward in Power Query, see attached. Right-click and refresh the green table.
    This is the M code in that file:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Split = Table.TransformColumns(Source,{{"Jeans", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}),
        ExpandedJeans = Table.ExpandListColumn(Split, "Jeans")
    in
        ExpandedJeans
    Attached Files Attached Files

Posting Permissions

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