Parse Numbers from Text to create Number List and add text to each value

Question

&nbsp; Trying to&nbsp; create a list from Range with added Text before (or after?)&nbsp; &nbsp;Combining Text from list.&nbsp;Goal:AreaExpandedLots 1 ‐ 10Lot 1, Lot 2, Lot 3, Lot 4, Lot 5, Lot 6, Lot 7, Lot 8, Lot 9, Lot 10Lots 5-6 &amp;9Lot 5, Lot 6, Lot 9Lots 3-5 &amp; 7-9Lot 3, Lot 4, Lot 5, Lot 7, Lot 8, Lot 9&nbsp;&nbsp;let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Area]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Custom], 1), type text),
    #"Split Column by Positions" = Table.SplitColumn(#"Inserted First Characters", "Custom", Splitter.SplitTextByPositions({1}), {"Custom.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Custom.1", Int64.Type}, {"First Characters", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Expanded", each Text.Combine(List.Transform(List.Transform(List.Numbers([First Characters],[Custom.1]),each Number.ToText(_)),each Text.Combine("Lot",(_))),","))
in
    #"Added Custom1"&nbsp;I know the previous Query may not work for multiple Ranges, perhaps a better way to Parse.Mostly stuck on the last line though:#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Expanded", each Text.Combine(List.Transform(List.Transform(List.Numbers([First Characters],[Custom.1]),each Number.ToText(_)),each Text.Combine("Lot",(_))),","))&nbsp;&nbsp;

marklaf · Accepted Answer

I think this gets what you want.&nbsp;let
    Source = OriginalAreas,
    ExpandLots = Table.AddColumn(
        Source,
        "Expanded",
        each
            [
                removeLots = Text.Trim(Text.Replace([Area], "Lots", "")),
                ampSplit = Text.Split(removeLots, "&amp;"),
                dashSplit = List.Transform(
                    ampSplit,
                    each [
                        split = Text.SplitAny(_, "-‐"), 
                        toNum = List.Transform(split, Number.FromText)
                    ][toNum]
                ),
                expandDash = List.Transform(dashSplit, each if List.Count(_) = 1 then _ else {_{0}.._{1}}),
                combo = List.Combine(expandDash),
                concatLot = "Lot " &amp; Text.Combine(List.Transform(combo, Text.From), ", Lot ")
            ][concatLot],
        type text
    )
in
    ExpandLots&nbsp;&nbsp;

Forum Discussion

Parse Numbers from Text to create Number List and add text to each value

2 Replies

Related content

number format help

Tooltip numbers

Number Format Issue

Need help creating week number

Non volatile random numbers between

Area	Expanded
Lots 1 ‐ 10	Lot 1, Lot 2, Lot 3, Lot 4, Lot 5, Lot 6, Lot 7, Lot 8, Lot 9, Lot 10
Lots 5-6 &9	Lot 5, Lot 6, Lot 9
Lots 3-5 & 7-9	Lot 3, Lot 4, Lot 5, Lot 7, Lot 8, Lot 9