Excel IP Mask and Subnet ID

Hello Again all,

I wonder if someone can advise if there is a simple way of getting the Mask Bits and Subnet ID. Say I have the following Info in the cells:

Cell (A) has the IP addresses eg. 192.168.0.1 and Cell (B) has the SubNet Mask of 255.255.255.0. What I would like to do is have excel work out what the mask and ID are. So in the example I gave Cell (C) would be Mask Bits "/24" and Cell (D) would be SubNet ID 192.168.0.0

Using http://www.subnet-calculator.com/ is great but not for lots of IP's and I don't want to do it manually lol.

I have added a sample file which has 2 sheets. Input sheet is the list of IP addresses I have an export from our systems and the result I am looking for is in the Output sheet. I can do this manually with an Online calculator but that will take me days

are you only working with class c addresses?

Hi,

Can you explain the rule/translation that gives the two results you expect from the two pieces of original data?

255.255.255.0 is a 24 bit mask therefore shorthand is /24 and would be the subnet 192.168.0.0 to 192.168.0.255 thats the premise!

255.255.255.0 /24
255.255.255.128 /25
255.255.255.192 /26
255.255.255.224 /27
255.255.255.240 /28
255.255.255.248 /29
255.255.255.252 /30
got to go out now but i probably have an excel subnet calculator somewhere

There's an Add-in with various IP UDFs here.

http://www.slideshare.net/rajivss/ip...ntationgen-v11

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

simple subnet calc attached

Martin,

That is just brilliant working great for Class C, can you amend this for Class A & B?

Thanks