List IP's in a list of ranges

[Danny54]

Does anyone know how to go about generating a list of ip's in list of ranges or seen it done using a macro?

Thanks

input sheet

test2	10.10.3.0-10.10.3.9, 10.10.3.11, 10.10.3.15-10.10.3.16
test3	199.10.3.0-199.10.3.9, 199.10.3.11, 199.10.3.15-199.10.3.16

desired output sheet

10.10.3.0
10.10.3.1
10.10.3.2
10.10.3.3
10.10.3.4
10.10.3.5
10.10.3.6
10.10.3.7
10.10.3.8
10.10.3.9
10.10.3.11
10.10.3.15
10.10.3.16
199.10.3.0
199.10.3.1
199.10.3.2
199.10.3.3
199.10.3.4
199.10.3.5
199.10.3.6
199.10.3.7
199.10.3.8
199.10.3.9
199.10.3.11
199.10.3.15
199.10.3.16

 

[sandy666]

now should be ok

IPs
10.10.3.0
10.10.3.1
10.10.3.2
10.10.3.3
10.10.3.4
10.10.3.5
10.10.3.6
10.10.3.7
10.10.3.8
10.10.3.9
10.10.3.11
10.10.3.15
10.10.3.16
199.10.3.0
199.10.3.1
199.10.3.2
199.10.3.3
199.10.3.4
199.10.3.5
199.10.3.6
199.10.3.7
199.10.3.8
199.10.3.9
199.10.3.11
199.10.3.15
199.10.3.16

 

[sandy666]

I forgot about M

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Split1 = Table.ExpandListColumn(Table.TransformColumns(Source, {{"raw", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"),
    Split2 = Table.SplitColumn(Table.TransformColumns(Split1,{{"raw", Text.Trim, type text}}), "raw", Splitter.SplitTextByAnyDelimiter({"-"}, QuoteStyle.Csv)),
    List = Table.AddColumn(Table.AddColumn(Table.AddColumn(Table.AddColumn(Split2, "raw.3", each if [raw.2] = null then [raw.1] else [raw.2]), "LC1", each Text.End([raw.1], 1), type text), "LC2", each Text.End([raw.3], 1), type text), "List", each {[LC1]..[LC2]}),
    Len = Table.TransformColumns(Table.AddColumn(Table.ExpandListColumn(List, "List"), "Length", each Text.Length([raw.1]), Int64.Type), {{"Length", each _ - 1, type number}}),
    TSC = Table.SelectColumns(Table.CombineColumns(Table.AddColumn(Len, "First Characters", each Text.Start([raw.1], [Length]), type text),{"First Characters", "List"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"IPs"),{"IPs"})
in
    TSC

?
and now

 

[Danny54]

Thanks guys. (Dante your solution worked - Super Job)

Im intrigued with the power query solution mentioned. I haven't coded anything in that arena.

Just to explain - I have a cell containing server ranges of IP's. they might all be in sequence or they might jump ranges.

example

10.0.0.0 - 10.0.0.2 would list only 3 ip's (10.0.0.0, 10.0.0.1, 10.0.0.2)

or

10.0.0.0 - 10.0.0.2, 10.0.0.5, 10.0.0.10-10.0.0.12 would list 7 ip's (10.0.0.0, 10.0.0.1, 10.0.0.2, 10.0.0.5, 10.0.0.10, 10.0.0.11, 10.0.0.12)

of course the decoded list would start on sheet2 - all in column "A1" going down the page in column "A"

Guys,
thanks so much for the help.

Have a great day!

 

[DanteAmor]

Thanks guys. (Dante your solution worked - Super Job)

Glad we could help & thanks for the feedback

 

[alansidman]

To learn more about Power Query, I urge you to obtain the book "M is for (Data) Monkey" on Amazon by Ken Puls and Miguel Escobar. It allows you to Mash up any worksheet.

 

[sandy666]

@Danny54
You are welcome
more about PowerQuery aka Get&Transform

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

@alansidman
do you already know how i did it?
(post#11 & 12)

@DanteAmor
Thanks again for post#9

 

[Danny54]

Again,

you guys are super. Thanks for the help.