+ Reply to Thread
Results 1 to 16 of 16

Calculate IP Address from Subnet Bit

  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Calculate IP Address from Subnet Bit

    I'm trying to find a script that will take the first octets of an ip address, the subnet bits and spit out a start and stop address range. I've searched and searched google, but can't find anything. Here's what I'm looking for. I start out with 002/8, which is easy enough to separate into columns, and I was able to do my entire list. What I need it to do is output 2.0.0.0 - 2.255.255.255

    Please Login or Register  to view this content.
    I did find a vb script, but I don't know if that can be used, in any way, in Excel.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate IP Address from Subnet Bit

    I don't see the logic that would translate 2.8 to 2.0.0.0 and 2.255.255.255.

    Why not 2.8.0.0 and 2.8.255.255? Or 2.8.0.0 and 2.255.255.255?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate IP Address from Subnet Bit Excel

    Quote Originally Posted by shg View Post
    I don't see the logic that would translate 2.8 to 2.0.0.0 and 2.255.255.255.

    Why not 2.8.0.0 and 2.8.255.255? Or 2.8.0.0 and 2.255.255.255?
    /8 is the subnet mask bit. It means the first octet, 002, doesn't change, but the other 3 do. It translates to 255.0.0.0, or 11111111.00000000.00000000.00000000. It means that every address beneath it has to start with 2.x.x.x, like 2.15.190.13...etc.
    Last edited by talkinggoat; 06-01-2010 at 02:57 PM.

  4. #4
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate IP Address from Subnet Bit

    Here is a page that explains subnetting, if it helps. I'm still trying to wrap my mind around how to do this.

    http://www.joshgentry.com/networking/subnet.htm

  5. #5
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate IP Address from Subnet Bit

    Maybe we could start with a script that takes 32 place holders, 1s and 0s, and divides them into groups of 8, separated by a decimal?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate IP Address from Subnet Bit

    What you want to do is probably simple, I just don't get it.

    What is the relevance of the 8 in 2/8?

  7. #7
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Smile Re: Calculate IP Address from Subnet Bit

    Quote Originally Posted by shg View Post
    What you want to do is probably simple, I just don't get it.

    What is the relevance of the 8 in 2/8?
    I fully understand. It took me a while to get the concept of subnetting, too. Each subnet is divided into 32 bits, split into 4 groups, called octets. We know these octets as our ip address, 192.168.1.12, for example.

    In this case, the 002 represents the first 3 numbers of an ip address, and the /8 represents the number of bits that are active (1) The subnet mask of 2.0.0.0 is 255.0.0.0. This means that in binary, the subnet mask is 11111111.00000000.00000000.00000000. For instance, when you type in your ip address of 192.168.1.x, and the subnet mask of 255.255.255.0, what you're telling it is your ip address's subnet is /24; there are 24 1's turned on in the subnet mask. 11111111.11111111.11111111.00000000. You can have a total of 32 1s turned on, in theory.

    It gets even more complex, because each one of these 1's stands for a number, that adds up to, or can add up to 255.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate IP Address from Subnet Bit

    I understand binary, thanks, and am familiar with how an IP address is composed.

  9. #9
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate IP Address from Subnet Bit

    I wasn't talking down to you, if you thought that.

    So, you understand the relevance of the 8, now?

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate IP Address from Subnet Bit

    No, it's the subnet mask I don't get -- which separates the host identifier (?) from the network identifier (?).

  11. #11
    Registered User
    Join Date
    05-31-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Calculate IP Address from Subnet Bit

    Oh, In this case, it's 8, or 255.0.0.0, because all 8 of the octets are used.The last 3 groups would be hosts, and the first (002) is the network. Does that make more sense?

  12. #12
    Registered User
    Join Date
    09-01-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Calculate IP Address from Subnet Bit

    hi, random outside comment here... xxx.xxx.xxx.xxx /8 just means you have 8 bits on.
    so its going to translate to a subnet mask of 255.0.0.0
    a /28 would come to 255.255.255.240
    28 bits on.

    8+8+8+8 8 bits per octet
    8 16 24 32 across octets bits compound

    28 comes after 24 so that tells you that the fourth octet is of interest. (if it were 23 it would be third octet)

    network id is always the first ip in the subnet
    and the broadcast is always the last.
    in my /28 example

    192.168.1.0 is net id
    192.168.1.15 is broadcast because .1.16 would be in the next subnet

    leaving you 192.168.1.1 -
    192.168.1.14 as usable IPs

    N-2=usuable ips
    where N=difference of 256 and the sum of the bits in the octet of interest



    crash course in subnetting lol
    im looking for this formula too.
    i saw a tutorial in counting out change using coin values i think they used "sum value" but i cant find the tutorial and i really dont know all the bells and whistles in excel which is why im here

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate IP Address from Subnet Bit

    Thanks for posting that. Now I (finally) get that /28 bits leaves 4 bits of freedom, 16 code slots.

    So what's the formula you're looking for?

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

    Re: Calculate IP Address from Subnet Bit

    Here are a few UDF related to Subnets that might help give you a start.

    It's a work in progress, I still haven't written the part that counts the number of addresses in a subnet.
    Attached Files Attached Files
    Gordon in Rovereto, Italy

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate IP Address from Subnet Bit

    Thank you for posting, I'm sure some of our members will find it useful.

  16. #16
    Registered User
    Join Date
    09-01-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Calculate IP Address from Subnet Bit

    I would like a worksheet where i can plug in:
    any ip, and subnet mask,
    Then output the ranges of the subnets along with Broadcast and Network IDs

    The idea i had used cells in a table with 4 rows and 8 columns.
    each row represents an octet and each column represents the value of the corresponding binary (128, 64, 32, etc.)
    Break down the subnet mask's octet values with the columns of binary then use formulas to work out the ranges in the nets based on the given ip.

    kind of complex to explain and im sure it is possible in excel i just dont have the skill.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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