+ Reply to Thread
Results 1 to 24 of 24

Creating IP Ranges from IP Add (Network) and Subnet Mask

  1. #1
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

    Exclamation 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

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    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..
    Attached Files Attached Files
    Last edited by zbor; 08-10-2010 at 03:01 AM.

  3. #3
    Registered User
    Join Date
    08-01-2010
    Location
    American in Rovereto, Italy
    MS-Off Ver
    Excel 2010 home & Excel 2010 work
    Posts
    46

    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.
    The result is the broadcast address
    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

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    Attached Files Attached Files
    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

  5. #5
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

    Smile Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

    Quote Originally Posted by martindwilson View Post
    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. #6
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

    Red face Re: Creating IP Ranges from IP Add (Network) and Subnet Mask

    Quote Originally Posted by dan_in-need-of-help View Post
    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
    Last edited by dan_in-need-of-help; 08-10-2010 at 10:10 AM. Reason: Needed to add additional information.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    have to a sample adress range that uses these masks
    Last edited by martindwilson; 08-10-2010 at 11:01 AM.

  8. #8
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

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

    thank you!!!

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

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

    dont thank too soon see my last post

  10. #10
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

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

    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

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

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

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

  13. #13
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

    Red face 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

  14. #14
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

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

    Any ideas??

  15. #15
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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.
    Last edited by martindwilson; 08-10-2010 at 06:13 PM.

  16. #16
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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. #17
    Registered User
    Join Date
    08-10-2010
    Location
    Utah
    MS-Off Ver
    Excel 2007
    Posts
    10

    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. #18
    Registered User
    Join Date
    08-05-2010
    Location
    Netherlands
    MS-Off Ver
    Excel 2003, 2007
    Posts
    2

    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
    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

  19. #19
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

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

    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.

  20. #20
    Forum Contributor Rhudi's Avatar
    Join Date
    03-08-2013
    Location
    South Carolina, US
    MS-Off Ver
    Professional Plus 2016 aka Office 365
    Posts
    188

    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. #21
    Forum Contributor Rhudi's Avatar
    Join Date
    03-08-2013
    Location
    South Carolina, US
    MS-Off Ver
    Professional Plus 2016 aka Office 365
    Posts
    188

    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?
    Last edited by Rhudi; 04-18-2013 at 12:49 PM. Reason: I'm stoopid

  22. #22
    Registered User
    Join Date
    08-28-2013
    Location
    Buenos Aires, Argentina
    MS-Off Ver
    Excel 2012
    Posts
    1

    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. #23
    Forum Contributor Rhudi's Avatar
    Join Date
    03-08-2013
    Location
    South Carolina, US
    MS-Off Ver
    Professional Plus 2016 aka Office 365
    Posts
    188

    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. #24
    Forum Contributor Rhudi's Avatar
    Join Date
    03-08-2013
    Location
    South Carolina, US
    MS-Off Ver
    Professional Plus 2016 aka Office 365
    Posts
    188

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1