Forum Discussion
Solved
Parse Numbers from Text to create Number List and add text to each value
Trying to create a list from Range with added Text before (or after?) Combining Text from list. Goal: Area Expanded Lots 1 ‐ 10 Lot 1, Lot 2, Lot 3, Lot 4, Lot 5, Lot 6, Lot 7, Lot 8,...
I think this gets what you want.
let Source = OriginalAreas, ExpandLots = Table.AddColumn( Source, "Expanded", each [ removeLots = Text.Trim(Text.Replace([Area], "Lots", "")), ampSplit = Text.Split(removeLots, "&"), 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 " & Text.Combine(List.Transform(combo, Text.From), ", Lot ") ][concatLot], type text ) in ExpandLots
MarkLaf
Super User
Super UserI think this gets what you want.
let
Source = OriginalAreas,
ExpandLots = Table.AddColumn(
Source,
"Expanded",
each
[
removeLots = Text.Trim(Text.Replace([Area], "Lots", "")),
ampSplit = Text.Split(removeLots, "&"),
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 " & Text.Combine(List.Transform(combo, Text.From), ", Lot ")
][concatLot],
type text
)
in
ExpandLots
Awesome That Works! Thanks So much! Much more complicated than my average expertise.