Splitting data from merged cells into separate rows

[NP1872]

Hi there, please see attached. Is there a quick way in excel or power query to split the data on the attached into separate rows. Would appreciate this.

 

[Hui]

Have a look at the second part of this post

How to Split Multiple Lines in a Cell into a Separate Cells / Columns

Learn how to spit multiple lines in a single cell into different cells using Text to Columns functionality. You need to use Control J to specify line break.

trumpexcel.com

 

[NP1872]

Hi there, please see attached. Is there a quick way in excel or power query to split the data on the attached into separate rows. Would appreciate this.

Thanks, this assists, but it puts the data into columns instead of rows. Is there a way to arrange in rows. Regards.

 

[p45cal]

2 goes in the attached, both very dodgy because they depend on there being the same number of lines in each cell in the same row.
1. See Power Query table at cell E5
2. See formula at cell I6 and copy across to cell K6
I would await a better answer!

 

[NP1872]

Its great how you managed to do this, excellent and it helps me a lot. I would like to know how you arranged this in power query as this is much quick option.

 

[pecoflyer]

This is the code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.FromList(List.Transform(Source[Date],Splitter.SplitTextByDelimiter("#(lf)")), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    ExpandedDate = Table.AddIndexColumn(Table.ExpandListColumn(Custom1, "Date"), "Index", 0, 1, Int64.Type),
    Custom2 = Table.FromList(List.Transform(Source[Description],Splitter.SplitTextByDelimiter("#(lf)")), Splitter.SplitByNothing(), {"Description"}, null, ExtraValues.Error),
    ExpandedDescription = Table.AddIndexColumn(Table.ExpandListColumn(Custom2, "Description"), "Index", 0, 1, Int64.Type),
    Custom3 = Table.FromList(List.Transform(Source[Amount],Splitter.SplitTextByDelimiter("#(lf)")), Splitter.SplitByNothing(), {"Amount"}, null, ExtraValues.Error),
    ExpandedAmount = Table.AddIndexColumn(Table.ExpandListColumn(Custom3, "Amount"), "Index", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(ExpandedDate, {"Index"}, ExpandedDescription, {"Index"}, "Description", JoinKind.LeftOuter),
    #"Expanded Description" = Table.ExpandTableColumn(#"Merged Queries", "Description", {"Description"}, {"Description"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Description", {"Index"}, ExpandedAmount, {"Index"}, "Amount", JoinKind.LeftOuter),
    #"Expanded Amount" = Table.ExpandTableColumn(#"Merged Queries1", "Amount", {"Amount"}, {"Amount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Amount",{"Index"})
in
    #"Removed Columns"