IP "Ranges" to singles

xcellerate

New Member
Joined
Jun 12, 2019
Messages
2
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try this

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

Code:
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top