IP "Ranges" to singles

[xcellerate]

So this is one IP scenario I've searched high and low for and can't believe I'm this "lucky" to be the only one looking for it. I have a long list of IP's (spanning across multiple c class or /24's) and it also has "ranges" in it and I'd like to have the ranges split out into single IP's. However, the reason I used "ranges" in quotes is because they are not the usual IP range format. My list looks like this:

111.16.2.20
111.16.2.44-111.16.2.46
111.16.2.50
111.16.2.58-111.16.2.59
111.16.2.94
111.16.2.114
111.16.2.116-111.16.2.120

Ideally formatted to:
111.16.2.20
111.16.2.44
111.16.2.45
111.16.2.46
111.16.2.50
111.16.2.58
111.16.2.59
111.16.2.94
111.16.2.114
111.16.2.116
111.16.2.117
111.16.2.118
111.16.2.119
111.16.2.120


I'm not even sure how to approach the fill in the missing Ip's from the range to get them added back in. Ideas?
Thanks!

 

[DanteAmor]

Try this

Assuming that your ip starts in cell A2 the results will be in B2 down

Sub IpRanges()
    Dim c As Range, i As Long, j As Long
    Dim gr As Variant, ips As Variant, ini As Variant, fin As Variant
    i = 2
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If InStr(1, c.Value, "-") > 0 Then
            ips = Split(c.Value, "-")
            gr = Trim(Left(WorksheetFunction.Substitute(ips(0), ".", Space(8), 3), 12))
            ini = Trim(Right(WorksheetFunction.Substitute(ips(0), ".", Space(4), 3), 4))
            fin = Trim(Right(WorksheetFunction.Substitute(ips(1), ".", Space(4), 3), 4))
            For j = ini To fin
                Cells(i, "B").Value = gr & "." & j
                i = i + 1
            Next
        Else
            Cells(i, "B").Value = c.Value
            i = i + 1
        End If
    Next
End Sub

 

[xcellerate]

Wow this works fantastic! The only test I did that failed is when the range spanned a different netblock like, 111.16.2.253-111.16.3.5
but with that said, I don't think that's actually been a real world scenario in my cases so I'm not going to be picky.
So far this seems to be working perfectly and am going to test out some more with bigger data sets!
Thank you!

 

[DanteAmor]

Wow this works fantastic! The only test I did that failed is when the range spanned a different netblock like, 111.16.2.253-111.16.3.5
but with that said, I don't think that's actually been a real world scenario in my cases so I'm not going to be picky.
So far this seems to be working perfectly and am going to test out some more with bigger data sets!
Thank you!

I'm glad to help you. Thanks for the feedback. Let me know any questions.