Creating IP Ranges from IP Add (Network) and Subnet Mask
PLEASE HELP ME
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:
IP Address Network: 10.15.32.0
Subnet Mask: 255.255.255.248
DESIRED OUTCOME: 10.15.32.1 to 10.15.32.6
Last edited by dan_in-need-of-help; 08-10-2010 at 12:54 PM. Reason: found issues with resolution
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..
Last edited by zbor; 08-10-2010 at 03:01 AM.
Row 1 10 15 32 0
Row 2 255 255 255 248
Then use this formula below each Octet pair
The result is the broadcast address
Please Login or Register to view this content.
10 15 32 7
your range is then 1 less than the broadcast address
and 1 more than the subnet ID
Gordon in Rovereto, Italy
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
Last edited by martindwilson; 08-10-2010 at 08:05 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Re: Creating IP Ranges from IP Add (Network) and Subnet Mask
that did it!!! THANK YOU!!!!!Originally Posted by martindwilson
Re: Creating IP Ranges from IP Add (Network) and Subnet Mask
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
Last edited by dan_in-need-of-help; 08-10-2010 at 10:10 AM. Reason: Needed to add additional information.
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
have to a sample adress range that uses these masks
Last edited by martindwilson; 08-10-2010 at 11:01 AM.
thank you!!!
dont thank too soon see my last post
Sorry I didn't finish reading your reply post. You asked me if I would ever use masks this large. The answer is YES. I use them all the time.
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
ok version 2
if you want them in on cell use in col E =c2&" - "&d2
Holy Moly!!!! This is awesome!!!!
THANK YOU
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:
network address mask from to
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??
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.
Last edited by martindwilson; 08-10-2010 at 06:13 PM.
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
If you have time, could you show me how to do that? I am a novice working with arrays and formulas and the like
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
Subnet__________Network IP_______Broadcast IP
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,
Last edited by sokolum; 02-28-2012 at 09:06 AM. Reason: post got mixed up
Forum Rules
Please read and adhere to these simple rules!
1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
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.
Also, advice: Upload example excel file.
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):
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.Please Login or Register to view this content.
I will followup with anything I figure out.
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:
Do what you need to set the IP and MASK values, then step through. Does this work? ... or am I brain dead?Please Login or Register to view this content.
Last edited by Rhudi; 04-18-2013 at 12:49 PM. Reason: I'm stoopid
This tread is very good for my!! Thanks for all; now my life is simplified
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.
Ok, less brute force. Here is a much better way:
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.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks