Parsing numbers from single cell

[Gregg Wolin]

I need to create a process that automates the population of a simple table. I manage the development of residential subdivisions and builders purchase lots within discreet “phases” of lots. When starting a new project, we agree on which lots are in each phase and the agreements list the phasing I the following format:

Phase 1: Lots 1-3, 16-20
Phase 2: Lots 4, 5, 7, 9-15
Phase 3: Lots 6, 8

I’d like the ability to enter the lot numbers for each phase in a single cell (as above) and have excel parse the numbers and populate a 3-column Phasing table.

I’m also open to other methods to accomplish this task (userform, wizards, etc.) but I thought I’d start with the most simple representation.

 

[AlanSidman]

Please upload to this site a workbook, showing sample data as entered and what your expected results should look like. Be sure that your sample is representative of your actual data. This will expedite and help to resolve your issue.

 

[Gregg Wolin]

Please upload to this site a workbook, showing sample data as entered and what your expected results should look like. Be sure that your sample is representative of your actual data. This will expedite and help to resolve your issue.

 

[Nebu]

Hi:

May be this? I have used power query to manipulate the data in the format you need.

Thanks

 

[Gregg Wolin]

Hi:

May be this? I have used power query to manipulate the data in the format you need.

Thanks

Doesn't seem to work (if I change the numbers)

 

[GraH - Guido]

Hi @Gregg Wolin , though it surely works. Did you use the refresh button after the update? In the result table click right mouse and select "refresh" from the menu.
Or did you got an error message after having added numbers (like phase 4)?

EDIT
This slightly adapted query might solve your problem.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    GetList = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column2],",")),
    ExpandLists = Table.ExpandListColumn(GetList, "Custom"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(ExpandLists, "Text Before Delimiter", each Text.BeforeDelimiter([Custom], "-"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Custom], "-"), type text),
    MakeList = Table.AddColumn(#"Inserted Text After Delimiter", "Custom.1", each try {Value.FromText([Text Before Delimiter])..Value.FromText([Text After Delimiter])} otherwise {[Text Before Delimiter]}),
    #"Removed Columns" = Table.RemoveColumns(MakeList,{"Custom", "Text Before Delimiter", "Text After Delimiter", "Column2"}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Column1]), "Column1", "Custom.1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 

[Gregg Wolin]

Hi @Gregg Wolin , though it surely works. Did you use the refresh button after the update? In the result table click right mouse and select "refresh" from the menu.
Or did you got an error message after having added numbers (like phase 4)?

My apologies - I didn't realize the table needed to be refreshed. I guess I need to learn some PowerQuery.
Thanks!

 

[GraH - Guido]

I thought so . Also I tested Nebu's solution and it is already working fine when you add items. So my previous EDIT in #6 can be dismissed.

As stated already many times now: Power Query is not so difficult as one may think. Already with the basics you go a long way on the road to wizardry.

 

[Chihiro]

I'd not recommend returning single table using staggered stack. Since dimensions for each phase will not match, it will create more headache than not and will bloat resulting table.

Personally I'd structure table like below.

Phase	LotNum	Index
Phase 1	1	1
Phase 1	2	2
Phase 1	3	3
Phase 1	16	4
Phase 1	17	5
Phase 1	18	6
Phase 1	19	7
Phase 1	20	8
Phase 2	4	1
Phase 2	5	2
Phase 2	7	3
Phase 2	9	4
Phase 2	10	5
Phase 2	11	6
Phase 2	12	7
Phase 2	13	8
Phase 2	14	9
Phase 2	15	10
Phase 3	6	1
Phase 3	8	2

Then put it into PivotTable to summarize using Index as row label.


See attached.

EDIT: First sentence for clarity.

 

[GraH - Guido]

Nice custom function, though I think List.Generate() may be a bit of a stretch for starters.
Added the index per phase in my solution as per your suggestion, Chihiro. I did not take the time to rename

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    GetList = Table.AddColumn(Source, "Custom", each Text.Split([Column2],",")),
    ExpandLists = Table.ExpandListColumn(GetList, "Custom"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(ExpandLists, "Text Before Delimiter", each Text.BeforeDelimiter([Custom], "-"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Custom], "-"), type text),
    MakeList = Table.AddColumn(#"Inserted Text After Delimiter", "Custom.1", each try {Value.FromText([Text Before Delimiter])..Value.FromText([Text After Delimiter])} otherwise {[Text Before Delimiter]}),
    #"Removed Columns" = Table.RemoveColumns(MakeList,{"Custom", "Text Before Delimiter", "Text After Delimiter", "Column2"}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom.1", {"Column1"}, {{"AsTable", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AsTable],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Custom.1", "Index"}, {"Custom.Custom.1", "Custom.Index"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom",{"AsTable"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Column1]), "Column1", "Custom.Custom.1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Phase 1", Int64.Type}, {"Phase 2", Int64.Type}, {"Phase 3", Int64.Type}})
in
    #"Changed Type"

 

[Nebu]

Hi:

Using group index.

Thanks

 

[Gregg Wolin]

Wow! I'd be lying if I said i had ANY idea how this works but I appreciate the solutions. I clearly need to invest some time with PowerQuery.