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

 

[DanteAmor]

Your data from the input sheet "Sheet1" like this:

varios 11abr2020.xlsm
	A	B
1
2	test2	10.10.3.0-10.10.3.9, 10.10.3.11, 10.10.3.15-10.10.3.16
3	test3	199.10.3.0-199.10.3.9, 199.10.3.11, 199.10.3.15-199.10.3.16
Sheet1

The output on sheet 2 in column A

Try this:

Sub List_IPs()
  Dim c As Range, n As Long, j As Long, fin As Long
  Dim a As Variant, b As Variant, d() As Variant
  '
  For Each c In Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B" & Rows.Count).End(3))
    For Each a In Split(c, ",")
      b = Split(a, "-")
      If UBound(b) = 0 Then fin = Mid(b(0), InStrRev(b(0), ".") + 1) Else fin = Mid(b(1), InStrRev(b(1), ".") + 1)
      For j = Mid(b(0), InStrRev(b(0), ".") + 1) To fin
        ReDim Preserve d(n)
        d(n) = Trim(Left(b(0), InStrRev(b(0), ".")) & j)
        n = n + 1
      Next j
    Next a
  Next c
  Sheets("Sheet2").Range("A2").Resize(n).Value = Application.Transpose(d)
End Sub

 

[sandy666]

just for fun, another approach with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Trim = Table.TransformColumns(Table.ExpandListColumn(Table.TransformColumns(Source, {{"raw", Splitter.SplitTextByAnyDelimiter({",","-"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"),{{"raw", Text.Trim, type text}})
in
    Trim

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

 

[alansidman]

Sandy, I came up with the same solution and then realized that the "-" indicated the OP was looking to add in the range of missing IPs. I could not figure out how to fill that range using PQ. Your thoughts on this.

 

[sandy666]

the "-" indicated the OP was looking to add in the range of missing IPs

really? I missed this
I will look at this again

 

[sandy666]

You'll need use last number from IPs which contain "-" then use something like this: try {[IfTAD1]..[TAD2]} otherwise null where IFTAD1 is the last number from first IP and TAD2 is a last number from the second IP (after "-") - it will give you a list of last numbers: eg. 0 1 2 3 4 5 6 7 8 9 then you can replace lat number of the first IPs with these number from the list
somehow you need eliminate IPs without "-"
I know , this is a bit twisted

10.10.3.0-10.10.3.9

 

[sandy666]

but I don't know if this should be treated the same way

like 11 12 13 14 15

 

[sandy666]

like this?
without situation mentioned in post#7

raw		IPs
10.10.3.0-10.10.3.9, 10.10.3.11, 10.10.3.15-10.10.3.16		10.10.3.0
199.10.3.0-199.10.3.9, 199.10.3.11, 199.10.3.15-199.10.3.16		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
		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

 

[DanteAmor]

but I don't know if this should be treated the same way

I understand that the OP wants the 2 ips:

 

[sandy666]

I understand that the OP wants the 2 ips:

if so the result is (edit) NOT ok, thanks Dante ?