Excel - Expand IP address range to individual IP ?

[wo1f]

If I've set of data like this in Ms Excel

10.0.0.1-10.0.0.3
172.16.0.3
192.168.0.2-192.168.0.2

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

10.0.0.1
10.0.0.2
10.0.0.3
172.16.0.3
192.168.0.2

iNZyC.png

[mehmetcik]

Put your data in column A starting at A1.

Then run this Macro

Sub Test()
LR = Cells(Rows.Count, 1).End(xlUp).Row
Data = Application.Transpose(Range("A1:A" & LR).Value)
For Count = 1 To UBound(Data)

If InStr(1, Data(Count), "-") > 0 Then
A = Split(Data(Count), "-")
If A(0) = A(1) Then
Output = Output & ", " & A(0)
Else
myBase = Left(A(0), InStrRev(A(0), "."))
For Count2 = Split(A(0), ".")(3) To Split(A(1), ".")(3)
Output = Output & ", " & myBase & Count2
Next
End If

Else
Output = Output & ", " & Data(Count)
End If
Next

If Len(Output) > 2 Then Output = Mid(Output, 3)
Temp = Split(Output, ",")
Range("B1:B" & UBound(Temp) + 1).Value = Application.Transpose(Temp)
End Sub