• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to populate Bullets and Sub-Bullets numbering



We have around 100 workbooks. Each workbook has a table. Each table grid has 100s of cells. Every cell has some bulleted text. Sample is given in attached excel in "Input text" sheet (I gave only 1 cell here but there is a table of such cells).

The problem is that these excels needs to be uploaded using a tool. However, that tool does not accept this format. It needs text in the format I gave in 2nd sheet (Desired Output).

Therefore, I now need to modify all these excels and which needs lots of manual work. This will lead us to lose our productive time.

Is there any way to sort this out using PowerQuery/VBA? I tried but count not build any logic :(




Thanks Alan for the help:). I can merge this to one cell.
I will test your solution once at my system. Thanks again.

GraH - Guido

Well-Known Member
A bit of a challenge, and since I do not completely understand Nebu's solution, I tried a different approach.
I'm not there yet to make it work on multiple rows of input. I guess converting this query into a function would be the key.

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ConvertToList = Table.AddColumn(Source, "TextAsList", each Text.Split([Text], "#(lf)")),
    GetPostionsOfLevel1 = Table.AddColumn(ConvertToList, "PositionsOfLevel1", each List.Combine({{0},List.Transform( List.PositionOf([TextAsList], "", Occurrence.All), each _ + 1)})),
    #"Expanded TextAsList" = Table.ExpandListColumn(GetPostionsOfLevel1, "TextAsList"),
    AddMainIndex = Table.AddIndexColumn(#"Expanded TextAsList", "Index", 0, 1),
    GetLevel = Table.AddColumn(AddMainIndex, "Levels", each if List.First(List.Intersect({{[Index]},[PositionsOfLevel1]})) = [Index] then "L1" else if [TextAsList] = "" then null else "L2"),
    GroupOnLevel = Table.Group(GetLevel, {"Levels"}, {{"All", each _, type table [Text=text, TextAsList=text, PositionsOfLevel1=list, Index=number, Levels=text]}}),
    Add_L1_Index = Table.AddColumn(GroupOnLevel, "Add_L1_Indx", each if [Levels] = "L1" then Table.AddIndexColumn([All], "L_Indx", 1,1) else [All]),
    Expand_Add_L1_Indx = Table.ExpandTableColumn(Add_L1_Index, "Add_L1_Indx", {"TextAsList", "Index", "Levels", "L_Indx"}, {"TextAsList", "Index", "Levels.1", "L_Indx"}),
    RemoveCols = Table.RemoveColumns(Expand_Add_L1_Indx,{"Levels","All"}),
    SortOnMainIndex = Table.Sort(RemoveCols,{{"Index", Order.Ascending}}),
    FillDown = Table.FillDown(SortOnMainIndex,{"L_Indx"}),
    GroupOnL1Index = Table.Group(FillDown, {"L_Indx"}, {{"All", each _, type table [TextAsList=text, Index=number, Levels.1=text, L_Indx=number]}}),
    Add_L2_Index = Table.AddColumn(GroupOnL1Index, "Add_L2_Indx", each Table.AddIndexColumn([All],"L2_Indx",0,1)),
    Expand_Add_L2_Indx = Table.ExpandTableColumn(Add_L2_Index, "Add_L2_Indx", {"TextAsList", "Index", "Levels.1", "L_Indx", "L2_Indx"}, {"TextAsList", "Index", "Levels.1", "L_Indx.1", "L2_Indx"}),
    RemoveCols1 = Table.RemoveColumns(Expand_Add_L2_Indx,{"L_Indx", "All"}),
    ConvertIndexesToText = Table.TransformColumnTypes(RemoveCols1,{{"L_Indx.1", type text}, {"L2_Indx", type text}}),
    ConcatenateIndexes = Table.AddColumn(ConvertIndexesToText, "Headers", each if [Levels.1] = null then null else if [L2_Indx] = "0" then [L_Indx.1]&". " else [L_Indx.1] &"."&  [L2_Indx]&" "),
    #"Split_Text_By_""-""" = Table.SplitColumn(ConcatenateIndexes, "TextAsList", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"TextAsList.1", "TextAsList.2"}),
    TrimText = Table.TransformColumns(#"Split_Text_By_""-""",{{"TextAsList.2", Text.Trim, type text}}),
    MergeHeadersWithText = Table.CombineColumns(TrimText,{"Headers", "TextAsList.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Paragraph Headers"),
    KeepOnlyFullHeaderCol = Table.SelectColumns(MergeHeadersWithText,{"Paragraph Headers"}),
    CombineInSingleCell = Text.Combine(Table.Column(KeepOnlyFullHeaderCol,"Paragraph Headers"),"#(lf)")
Results in

Side note: I failed to do this with some further use and a combination of List functions: List.Positions, List.Transform. Hard-coding works, but replacing these with the column references containing lists returns an error:confused:. Though I'm convinced the same result can be achieved with them in a script that is perhaps 4-6 steps.