Excel - Expand IP address range to individual IP ?

[Danny54]

Excel - Expand IP address range to individual IP ?

A
10.0.0.1-10.0.0.3
172.16.0.3
192.168.0.2-192.169.0.25 <- notice the range starts at 192.168.x.x and goes thru 192.169.x.x


How do I expand those IP Address and produce an output like this?


A B
10.0.0.1-10.0.0.3 10.0.0.1
172.16.0.3 10.0.0.2
192.168.0.2-192.179.1.25 10.0.0.3
172.16.0.3
192.168.0.2
192.168.0.3
.
.
92.169.1.25

Thanks

 

[bobsan42]

Present the information in a readable and understandable form so anyone willing to help can understand what you mean.
I had to stare at it more than a minute to solve the puzzle.
Explain the purpose or preferred method if you expect adequate solution.

 

[Danny54]

Yes, I noticed that when I pasted the values they all were shifted. Let me try it this way

read in sheet1 to get the IP' range values
output to sheet2 all separate values found in each IP range and dept

so the first line is companya with a range of 10.223.227.64, 10.223.227.65, 10.223.227.66
write those to the new sheet
read the next row of sheet1 companya with a range of 10.223.227.252, 10.223.227.253, 10.223.227.254, 10.223.227.255
write those below the last output
ect....

Thanks

Input Sheet1

Dept	IP Range
Companya	10.223.227.64-10.223.227.66
Companya	10.223.227.252-10.223.227.255
Companya	10.10.10.1
Companya	192.168.20.20
Companyb	10.202.226.128-10.202.226.130
Companyb	192.12.12.15
Companyb	199.0.0.10-199.0.0.12

Output Sheet2

Dept	Targets
Companya	10.223.227.64
Companya	10.223.227.65
Companya	10.223.227.66
Companya	10.223.227.252
Companya	10.223.227.253
Companya	10.223.227.254
Companya	10.223.227.255
Companya	10.10.10.1
Companya	192.168.20.20
Companyb	10.202.226.128
Companyb	10.202.226.129
Companyb	10.202.226.130
Companyb	192.12.12.15
Companyb	199.0.0.10
Companyb	199.0.0.11
Companyb	199.0.0.12

 

[bobsan42]

Here is a solution using powerQuery. Turn the data range into a table and replace IPTable with your table name (in the Source line of the following M-code):

let
    Source = Excel.CurrentWorkbook(){[Name="IPTable"]}[Content],

    splitIPRange = Table.SplitColumn(Source, "IP Range", Splitter.SplitTextByDelimiter("-", QuoteStyle.None), {"IPSplit1", "IPSplit2"}),
    ip1Copy = Table.DuplicateColumn(splitIPRange, "IPSplit1", "IP1Copy"),
    ip2Copy = Table.DuplicateColumn(ip1Copy, "IPSplit2", "IP2Copy"),
    split2 = Table.SplitColumn(ip2Copy, "IP1Copy", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.None, true), {"IPpart1", "IPStartNum"}),
    split3 = Table.SplitColumn(split2, "IP2Copy", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.None, true), {"IPpart12", "IPEndNum"}),
    remove1 = Table.RemoveColumns(split3,{"IPpart12"}),

    replace1= Table.ReplaceValue(remove1,null, each _[IPStartNum],Replacer.ReplaceValue,{"IPEndNum"}),
    typechange = Table.TransformColumnTypes(replace1,{{"IPStartNum", Int64.Type}, {"IPEndNum", Int64.Type}}),
    listnums = Table.AddColumn(typechange,"CustomList",each List.Numbers([IPStartNum],[IPEndNum]-[IPStartNum]+1)),
    expandList = Table.ExpandListColumn(listnums, "CustomList"),
    mergeip = Table.CombineColumns(Table.TransformColumnTypes(expandList, {{"CustomList", type text}}, "bg-BG"),{"IPpart1", "CustomList"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"IPList"),
    cleanup = Table.RemoveColumns(mergeip,{"IPSplit1", "IPSplit2", "IPStartNum", "IPEndNum"})
in
    cleanup

.................
	A	B	C
1
2
3		Dept	IP Range
4		Companya	10.223.227.64-10.223.227.66
5		Companya	10.223.227.252-10.223.227.255
6		Companya	10.10.10.1
7		Companya	192.168.20.20
8		Companyb	10.202.226.128-10.202.226.130
9		Companyb	192.12.12.15
10		Companyb	199.0.0.10-199.0.0.12
Source

..................
	A	B
1	Dept	IPList
2	Companya	10.223.227.64
3	Companya	10.223.227.65
4	Companya	10.223.227.66
5	Companya	10.223.227.252
6	Companya	10.223.227.253
7	Companya	10.223.227.254
8	Companya	10.223.227.255
9	Companya	10.10.10.1
10	Companya	192.168.20.20
11	Companyb	10.202.226.128
12	Companyb	10.202.226.129
13	Companyb	10.202.226.130
14	Companyb	192.12.12.15
15	Companyb	199.0.0.10
16	Companyb	199.0.0.11
17	Companyb	199.0.0.12
Result

 

[Danny54]

Super.. Thanks..

One question, what do I need to modify if IP Range is as below.
You see the starting range is 10.223.227.0 and spans to 10.223.228.255

Dept	IP Range
Companya	10.223.227.0-10.223.228.255

 

[bobsan42]

Without further modifications to the m code you have to split it in two rows. I didn't forsee this case - i expected that only the last part of the ip will change.

 

[Danny54]

Good solution. Thanks so much for the help

 

[lrobbo314]

Here is my take on the PQ.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    X = Table.TransformColumns(Source,{{"IP Range", each
    let 
        s = Text.Split(_,"-"),
        root = Text.BeforeDelimiter(s{0}, ".", 2),
        min = Number.From(Text.AfterDelimiter(s{0},".",2)),
        max = Number.From(Text.AfterDelimiter(s{List.Count(s)-1}, ".", 2)),
        range = List.Generate(()=> min, each _ <= max, each _ +1, each root & "." & Text.From(_))
    in
        range
    }}),
    Expand = Table.ExpandListColumn(X, "IP Range")
in
    Expand

Not sure how you would go about doing it with the added caveat in Post #5.

So, how about this using VBA.

Sub ExpandIP()
Dim IP() As Variant:        IP = Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value2
Dim SD As Object:           Set SD = CreateObject("System.Collections.ArrayList")
Dim IPR() As String

For i = 1 To UBound(IP)
    IPR = Split(IP(i, 2), "-")
    If UBound(IPR) > 0 Then
        X IPR(0), IPR(UBound(IPR)), IP(i, 1), SD
    Else
        SD.Add Join(Array(IP(i, 1), IP(i, 2)), ",")
    End If
Next i

With Range("H1:I1")
    .Value = Array("Dept", "IP Range")
    .Font.Bold = True
End With

With Range("H2").Resize(SD.Count)
    .Value = Application.Transpose(SD.toArray)
    .TextToColumns DataType:=xlDelimited, Comma:=True
End With
End Sub

Sub X(minIP As String, maxIP As Variant, Comp As Variant, SD As Object)
Dim SP() As String
Dim depth As Integer

SD.Add Join(Array(Comp, minIP), ",")
SP = Split(minIP, ".")

Do Until minIP = maxIP
    SP(3) = SP(3) + 1
    CheckIP SP
    minIP = Join(SP, ".")
    SD.Add Join(Array(Comp, minIP), ",")
Loop
End Sub

Function CheckIP(SP As Variant)
For i = UBound(SP) To 1 Step -1
    If SP(i) = 256 Then
        SP(i) = 0
        SP(i - 1) = SP(i - 1) + 1
    End If
Next i
CheckIP = SP
End Function

ExpandIP.xlsm
	A	B	C	D	E	F	G	H	I
1	Dept	IP Range		Dept	IP Range			Dept	IP Range
2	Companya	10.223.227.64-10.223.227.66		Companya	10.223.227.64			Companya	10.223.227.64
3	Companya	10.223.227.252-10.223.227.255		Companya	10.223.227.65			Companya	10.223.227.65
4	Companya	10.10.10.1		Companya	10.223.227.66			Companya	10.223.227.66
5	Companya	192.168.20.20		Companya	10.223.227.252			Companya	10.223.227.252
6	Companyb	10.202.226.128-10.202.226.130		Companya	10.223.227.253			Companya	10.223.227.253
7	Companyb	192.12.12.15		Companya	10.223.227.254			Companya	10.223.227.254
8	Companyb	199.0.0.10-199.0.0.12		Companya	10.223.227.255			Companya	10.223.227.255
9	Companyc	10.223.227.0-10.223.228.255		Companya	10.10.10.1			Companya	10.10.10.1
10				Companya	192.168.20.20			Companya	192.168.20.20
11				Companyb	10.202.226.128			Companyb	10.202.226.128
12				Companyb	10.202.226.129			Companyb	10.202.226.129
13				Companyb	10.202.226.130			Companyb	10.202.226.130
14				Companyb	192.12.12.15			Companyb	192.12.12.15
15				Companyb	199.0.0.10			Companyb	199.0.0.10
16				Companyb	199.0.0.11			Companyb	199.0.0.11
17				Companyb	199.0.0.12			Companyb	199.0.0.12
18								Companyc	10.223.227.0
19								Companyc	10.223.227.1
20								Companyc	10.223.227.2
21								…	…
22								Companyc	10.223.228.254
23								Companyc	10.223.228.255
Sheet5

 

[Danny54]

Thank you. I just tried the VBa and the results were exactly what was needed. Using the VBa I wont have to duplicate the lines where the IP's go over the 255 range in the lower part of the IP.

 

[Danny54]

Thanks bobsan42 and Irobbo314 for your solutions. I've studied both and have learned new techniques for creating future solutions.

​