1. ## Creating IP Ranges from IP Add (Network) and Subnet Mask

I have a situation where I have ten's of thousands of IP Addresses (the Network portion) and the Subnet Mask. I need to somehow create another field in Excel 2007 that will show the IP Address Range based on the two fields.

Example:

DESIRED OUTCOME: 10.15.32.1 to 10.15.32.6

2. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

This looks very complicated to me so there must be something easier..

However, there would be much easier approach if there are some rules as:

Every IP address ends with 0
Every desired outcome ends with 1 up to (depending of subnet mask)...

Below solution is for any IP address

Values are entered as text..

3. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Row 1 10 15 32 0
Row 2 255 255 255 248
Then use this formula below each Octet pair
``Please Login or Register  to view this content.``
10 15 32 7
and 1 more than the subnet ID

4. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

try this ads 1 to network and rest determined by mask from lookup sheet 2
zbor yours fails on
10.10.10.192
255.255.255.224
it returns
10.10.10.192 to 10.10.10.192
it should be
10.10.10.193 10.10.10.222
valid hosts for .224 (/27) are
10.10.10.1 10.10.10.30
10.10.10.33 10.10.10.62
10.10.10.65 10.10.10.94
10.10.10.97 10.10.10.126
10.10.10.129 10.10.10.158
10.10.10.161 10.10.10.190
10.10.10.193 10.10.10.222
10.10.10.225 10.10.10.254

5. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Originally Posted by martindwilson
try this ads 1 to network and rest determined by mask from lookup sheet 2
zbor yours fails on
10.10.10.192
255.255.255.224
it returns
10.10.10.192 to 10.10.10.192
it should be
10.10.10.193 10.10.10.222
valid hosts for .224 (/27) are
that did it!!! THANK YOU!!!!!

6. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Originally Posted by dan_in-need-of-help
that did it!!! THANK YOU!!!!!
I spoke to soon!

The formula does a GREAT job if the subnet mask is "255.255.255.x", but when the subnet mask is "255.255.x.0" then it breaks.

Example: The result of this ip address pair: 10.0.6.0, 255.255.254.0 gives me this result: 10.0.6.1 (first column) 10.0.6.510 (second column).

The second column should be 10.0.7.254

Any ideas?

Here is the information I added on Sheet 2:
255.255.255.252 2
255.255.255.248 6
255.255.255.240 14
255.255.255.224 30
255.255.255.192 62
255.255.255.128 126
255.255.255.0 254
255.255.254.0 510
255.255.252.0 1,022
255.255.248.0 2,046
255.255.240.0 4,094
255.255.224.0 8,190
255.255.192.0 16,382
255.255.128.0 32,766

7. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

ah you never mentioned you were going to have masks that big! i'll get on it!
hmm id need to think 10.10.0.0 255.255.254.0
would give 512 ip's 510 usable
10.10.0.1 to 10.10.0.255
10.10.1.0 to 10.10.1.254
or to put it anotherway
10.10.0.1 to 10.10.1.254
are you sure you want this, its unlikely youll ever use masks like that for hosts maybe for networks

thank you!!!

9. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

dont thank too soon see my last post

10. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

In the example you posted, the result ended up using two lines. Is there a way to consolidate the two lines into one? So, in your example you used 10.10.0.0 with a subnet mask of 255.255.254.0 yielding a result of two ranges: 10.10.0.1 - 10.10.0.255 and 10.10.1.0 - 10.10.1.254

Is it possible to have the result be a "one-liner"? Example: 10.10.0.1 - 10.10.1.254

11. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

ok version 2
if you want them in on cell use in col E =c2&" - "&d2

12. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Holy Moly!!!! This is awesome!!!!
THANK YOU

13. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Alas.... there are issues with the code.

Attached is the spreadsheet which has the formulas applied. As you can see there are many results with a #N/A in them.

Can you see what might be missing?

THANK YOU AGAIN!!!

PS> I've tried to upload the file but I keep getting database errors (???). It is an 800k file. here is a screen shot of a couple of lines with the error:

10.0.1.0 255.255.255.0 10.0.1.1 10.0.1.254
10.0.2.0 255.255.255.0 10.0.2.1 10.0.2.254
10.0.3.0 255.255.255.0 10.0.3.1 10.0.3.254
10.0.4.0 255.255.255.0 10.0.4.1 10.0.4.254
10.0.5.0 255.255.255.0 10.0.5.1 10.0.5.254
10.0.6.0 255.255.254.0 10.0.6.1 #N/A
10.0.8.0 255.255.254.0 10.0.8.1 #N/A
10.0.16.0 255.255.240.0 10.0.16.1 #N/A
10.3.25.0 255.255.255.192 10.3.25.1 10.3.25.62

Any ideas??

15. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

its late and ive been out, but i did think oh what if say
10.0.8.0 255.255.254.0 on a class b but you cant have 10.xxx.xxx.xxx as class b
128.0.8.0 255.255.254.0 however would give 128.0.8.1 - 128.0.9.254
i think a bigger lookup table is needed hmm thers are just to many thingd
need to determine whether class b or c ,beware of conflicting masks. i think that's why there's not an excel template for this.

16. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

just has another rethink
should be possible to create a matrix based on mask with all variations(lets forget what's valid, I assume yours will be)
using the first bit i gave you up to 255.255.255.0
then picking of the third octet value to determine which range to start from

17. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

If you have time, could you show me how to do that? I am a novice working with arrays and formulas and the like

18. ## Creating 'Network IP' and 'Subnet IP' Ranges from subnets

Very interesting approach, although i thought those calculations would only possible with VBA.
I tried myself to adjust the calculations to only get network/broadcast ranges... but to make this kind of calculations is beyond me.

Unlike 'dan_in-need-of-help', my approach is to get only network/broadcast ranges, like (see examples):

COLUMN 1_______COLUMN 2_______COLUMN 3

192.168.10.0/24___192.168.10.0___192.168.0.255
172.29.31.5/32____172.29.31.5____172.29.31.5
10.0.29.128/25____10.0.29.128____10.0.29.255
10.133.11.32/27___10.133.11.32___10.133.11.62
10.50.0.0/18______10.50.0.0______10.50.63.254

If the Bitmask notation '/24' is more difficult to use it, then use the netmask notation '255.255.255.0'.
I want to be able to creating ranges starting from /8 untill /32.

If someone have an idea or a sheet to share, it would be welcome!

reg,

19. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Forum Rules

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more hours have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

20. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

I was trying to do something sort of related, that might be helpful here. What about "SPLIT(" and "JOIN("?
I used them to break apart an IP address and MASK to do some things. But, now I want do do the same thing. Actually... if I have any IP address and apply a mask to it, I want the range that IP/MASK pair would use.
So, here is the core of my thought (done in Excel 2010):
``Please Login or Register  to view this content.``
Basically, an IPV4 address is like a 4-digit Base255 number. My code will die horibly if the +1 should cause a 'carry'. But, I'll figure that out later. Right now, my data will never do that to me.

I will followup with anything I figure out.

21. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Ok, here is what I figured out. This is ugly. This is brute force. But, it works. I know you excel gurus out there will chuckle. I do not know how to create/use arrays.
Well, here goes:
``Please Login or Register  to view this content.``
Do what you need to set the IP and MASK values, then step through. Does this work? ... or am I brain dead?

22. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

This tread is very good for my!! Thanks for all; now my life is simplified

23. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

I've come up with an extremely brute force way to convert a Dotted MASK (ie 255.255.255.192) to CIDR (ie /26). There has got to be a more elegant way to do this.
Here is some partial code:
``Please Login or Register  to view this content.``

24. ## Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

Ok, less brute force. Here is a much better way:
``Please Login or Register  to view this content.``
The DecToBin function is listed here (copied/tweaked from another public post):
``Please Login or Register  to view this content.``
The original function did a "For n = 31 To...", but as valid mask values will never be higher than "255", the loop can be shorter.

There are currently 1 users browsing this thread. (0 members and 1 guests)