Split Rows?

[beagleton]

Hello All,

I have a spreadsheet where there are numbered lists in individual cells. It looks like this:

Number	Name	Issue
1	Name A	1. Issue A1 2. Issue A2 3. Issue A3
2	Name B	1. Issue B1 2. Issue B2 3. Issue B3
3	Name C	1. Issue C1 2. Issue C2 3. Issue C3

What I would like to do is:

Number	Name	Issue
1	Name A	Issue A1
2	Name A	Issue A2
3	Name A	Issue A3
4	Name B	Issue B1
5	Name B	Issue B2
6	Name B	Issue B3
7	Name C	Issue C1
8	Name C	Issue C2
9	Name C	Issue C3

Does anyone know how to do this/ if this is possible? Any help is appreciated.
Brendan

 

[Ken Puls]

Hey Brendan,

Right click the Issue column --> Split by Delimiter
Split at custom
Expand the Advanced options and choose to split into Rows
Check the box for special characters

You may need to use the LineFeed or the Carriage Return, but one of them should work.

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Name", type text}, {"Issue", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Issue", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Issue"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Issue", type text}})
in
    #"Changed Type1"

 

[beagleton]

Thank you very much. Worked perfectly.
B

 

[beagleton]

As an interesting side note, the "split into rows" option is not present in Excel 2010. the M still works even without this option. I see it present in 2016 though