Search code examples
excelexcel-formulaformulapowerquery

Excel Formula to split columns where cells may contain multiple values into another column, where each cell only contains a single value

I have a column in Excel that looks like the below:

Sample

And what I want is a formula that can copy the column into the next column splitting each cell with multiple values so that each cell will only contain a single value. Similar to this:

enter image description here

Thanks for your help!

Solution

  • Use Powerquery, Split by linefeed and into new rows =)

    Let's imagine this set of data:

    enter image description here

    • Select your data, A1:A7 in this example.
    • Import this data into PowerQuery AKA Get&Transform (including headers).
    • Under "Home" click, "Split Column" and choose "By Delimiter".
    • Use a custom delimiter: #(lf) and under advanced tick "Rows".
    • Hit "OK" and close PQ. Your results are there.

    enter image description here