# Subnet calculator for MS Excel

I quite often use the handy subnet calculator here:

http://www.subnet-calculator.com/

Which I find very useful. However, does anyone have details/a link for a version of this that offers similar functionality but is in an excel spreadsheet.

I could then include it in a worksheet and I wouldn't have to keep visiting the website. Also handy if I don't have internet access - which can often be the case when I need it.

-
Zoredache told me to use my brain – l0c0b0x Sep 29 '09 at 16:43
All you have to do is learn how to count by powers of 2. – Zoredache Oct 7 '09 at 5:03
I opened this question expecting to comment "just do it in your head" but you beat me to it! – Chopper3 Oct 6 '10 at 19:47
Related to "Do it in your head": serverfault.com/questions/49765/how-does-ipv4-subnetting-work – voretaq7 Dec 16 '12 at 6:16

Here's a pretty basic one. It's actually a pretty simple task to create your own.

-
I know it's simple, but time is precious. – Martin Sep 29 '09 at 17:28
Fair enough!--- – Izzy Sep 29 '09 at 18:37

I know its an old thread but...

This is a VBA Function for Excel. I'm going to assume you know how to add a VBA Function ( Or Macro etc ), If not I'll hope someone will reply to this thread with a simple How To.

Its very basic, but it does exactly what is says on the tin and works for me.

Input is =IPSubnetCalc(X, YY.YY.YY.YY/ZZ )

X = 1 to 5 - See below for what each option does. Y = IPv4 Address Z = 8 to 32, Excl 31. ( BitMask )

Obviously, the X, Y/Z can be cell references.

Enjoy

``````Public Function IPSubnetCalc(intFn%, IPandMaskInput\$)

'# IP Subnet Calculator by Marcus Mansfield #
'# Input Expected xx.xx.xx.xx/yy            #
'# Where xx = decimal IPv4 Octet            #
'#       yy = BitMask Value ( 0 - 32 ex.31 )#
'# Functions ...                            #
'# 1 - Return Subnet Mask in dotted decimal #
'# 2 - Return Subnet Address in Dot. Dec.   #
'# 4 - Return Lowest Client IP in Dot. Dec. #
'# 5 - Return Highest Client IP in Dot. Dec.#

Dim IPOctet1%, IPOctet2%, IPOctet3%, IPOctet4%
Dim Dot1%, Dot2%, Dot3%, Slash1%
Dim IPNetwork1BIN\$, IPNetwork2BIN\$, IPNetwork3BIN\$, IPNetwork4BIN\$

On Error Resume Next

Dot2 = InStr(Dot1 + 1, IPandMaskInput, ".")
Dot3 = InStr(Dot2 + 1, IPandMaskInput, ".")

'Check Basic Format
If Dot1 = 0 Or Dot2 = 0 Or Dot3 = 0 Or Slash1 = 0 Then
IPSubnetCalc = "Incorrect Format"
Exit Function
End If

'Split IP into 8bit Octets
IPOctet1 = Mid(IPandMaskInput, 1, Dot1 - 1)
IPOctet2 = Mid(IPandMaskInput, Dot1 + 1, Dot2 - Dot1 - 1)
IPOctet3 = Mid(IPandMaskInput, Dot2 + 1, Dot3 - Dot2 - 1)
IPOctet4 = Mid(IPandMaskInput, Dot3 + 1, Slash1 - Dot3 - 1)

If IPOctet1 > 255 Or IPOctet2 > 255 Or IPOctet3 > 255 Or IPOctet4 > 255 Then
IPSubnetCalc = "Value Error, Octet > 255"
Exit Function
End If
If IPOctet1 < 0 Or IPOctet2 < 0 Or IPOctet3 < 0 Or IPOctet4 < 0 Then
IPSubnetCalc = "Value Error, Octet < 0"
Exit Function
End If
IPSubnetCalc = "Bitmask Error : Range 8 - 32, Excl. 31"
Exit Function
End If

Select Case intFn
Case 1 'Subnet Mask Dot. Dec.
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

Case 2 'Subnet Dot. Dec.
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

IPNetwork1BIN = ""
IPNetwork2BIN = ""
IPNetwork3BIN = ""
IPNetwork4BIN = ""

For iCounter = 1 To 8

If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
IPNetwork1BIN = IPNetwork1BIN & "1"
Else
IPNetwork1BIN = IPNetwork1BIN & "0"
End If

If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
IPNetwork2BIN = IPNetwork2BIN & "1"
Else
IPNetwork2BIN = IPNetwork2BIN & "0"
End If

If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
IPNetwork3BIN = IPNetwork3BIN & "1"
Else
IPNetwork3BIN = IPNetwork3BIN & "0"
End If

If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
IPNetwork4BIN = IPNetwork4BIN & "1"
Else
IPNetwork4BIN = IPNetwork4BIN & "0"
End If

Next

BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

BuildBIN = ""

For iCounter = 1 To 32
BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
Else
BuildBIN = BuildBIN & "1"
End If
Next

Case 4 'Low IP Dot Dec
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

IPNetwork1BIN = ""
IPNetwork2BIN = ""
IPNetwork3BIN = ""
IPNetwork4BIN = ""

For iCounter = 1 To 8

If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
IPNetwork1BIN = IPNetwork1BIN & "1"
Else
IPNetwork1BIN = IPNetwork1BIN & "0"
End If

If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
IPNetwork2BIN = IPNetwork2BIN & "1"
Else
IPNetwork2BIN = IPNetwork2BIN & "0"
End If

If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
IPNetwork3BIN = IPNetwork3BIN & "1"
Else
IPNetwork3BIN = IPNetwork3BIN & "0"
End If

If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
IPNetwork4BIN = IPNetwork4BIN & "1"
Else
If iCounter = 8 Then
IPNetwork4BIN = IPNetwork4BIN & "1"
Else
IPNetwork4BIN = IPNetwork4BIN & "0"
End If
End If

Next

Case 5 'High IP Dot Dec
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

BuildBIN = ""

For iCounter = 1 To 32
BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
Else
If iCounter = 32 Then
BuildBIN = BuildBIN & "0"
Else
BuildBIN = BuildBIN & "1"
End If
End If
Next

Case Else 'Incorrect Selection
IPSubnetCalc = "Incorrect Function"
End Select

End Function
``````
-

I've added a new option to calculate the number of available hosts in a network.

Hope it helps.

Rui

``````Public Function IPSubnetCalc(intFn%, IPandMaskInput\$)

' PROGRAM:
' IP Subnet Calculator by Marcus Mansfield
'
' CHANGES:
' Date: 2010-10-06
' Author: Rui Sousa (ruib.sousa@gmail)
' Change: Add funtion 6 to return number os hosts
'
' INPUTS:
' Input Expected xx.xx.xx.xx/yy
' Where xx = decimal IPv4 Octet
'       yy = BitMask Value ( 0 - 32 ex.31 )
'
'
' FUNCTIONS:
' 1 - Return Subnet Mask in dotted decimal
' 2 - Return Subnet Address in Dot. Dec.
' 4 - Return Lowest Client IP in Dot. Dec.
' 5 - Return Highest Client IP in Dot. Dec.
'

Dim IPOctet1%, IPOctet2%, IPOctet3%, IPOctet4%
Dim Dot1%, Dot2%, Dot3%, Slash1%
Dim IPNetwork1BIN\$, IPNetwork2BIN\$, IPNetwork3BIN\$, IPNetwork4BIN\$

On Error Resume Next

Dot2 = InStr(Dot1 + 1, IPandMaskInput, ".")
Dot3 = InStr(Dot2 + 1, IPandMaskInput, ".")

'Check Basic Format
If Dot1 = 0 Or Dot2 = 0 Or Dot3 = 0 Or Slash1 = 0 Then
IPSubnetCalc = "Incorrect Format"
Exit Function
End If

'Split IP into 8bit Octets
IPOctet1 = Mid(IPandMaskInput, 1, Dot1 - 1)
IPOctet2 = Mid(IPandMaskInput, Dot1 + 1, Dot2 - Dot1 - 1)
IPOctet3 = Mid(IPandMaskInput, Dot2 + 1, Dot3 - Dot2 - 1)
IPOctet4 = Mid(IPandMaskInput, Dot3 + 1, Slash1 - Dot3 - 1)

If IPOctet1 > 255 Or IPOctet2 > 255 Or IPOctet3 > 255 Or IPOctet4 > 255 Then
IPSubnetCalc = "Value Error, Octet > 255"
Exit Function
End If
If IPOctet1 < 0 Or IPOctet2 < 0 Or IPOctet3 < 0 Or IPOctet4 < 0 Then
IPSubnetCalc = "Value Error, Octet < 0"
Exit Function
End If
IPSubnetCalc = "Bitmask Error : Range 8 - 32, Excl. 31"
Exit Function
End If

Select Case intFn
Case 1 'Subnet Mask Dot. Dec.
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

Case 2 'Subnet Dot. Dec.
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

IPNetwork1BIN = ""
IPNetwork2BIN = ""
IPNetwork3BIN = ""
IPNetwork4BIN = ""

For iCounter = 1 To 8

If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
IPNetwork1BIN = IPNetwork1BIN & "1"
Else
IPNetwork1BIN = IPNetwork1BIN & "0"
End If

If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
IPNetwork2BIN = IPNetwork2BIN & "1"
Else
IPNetwork2BIN = IPNetwork2BIN & "0"
End If

If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
IPNetwork3BIN = IPNetwork3BIN & "1"
Else
IPNetwork3BIN = IPNetwork3BIN & "0"
End If

If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
IPNetwork4BIN = IPNetwork4BIN & "1"
Else
IPNetwork4BIN = IPNetwork4BIN & "0"
End If

Next

BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

BuildBIN = ""

For iCounter = 1 To 32
BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
Else
BuildBIN = BuildBIN & "1"
End If
Next

Case 4 'Low IP Dot Dec

BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

IPNetwork1BIN = ""
IPNetwork2BIN = ""
IPNetwork3BIN = ""
IPNetwork4BIN = ""

For iCounter = 1 To 8

If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
IPNetwork1BIN = IPNetwork1BIN & "1"
Else
IPNetwork1BIN = IPNetwork1BIN & "0"
End If

If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
IPNetwork2BIN = IPNetwork2BIN & "1"
Else
IPNetwork2BIN = IPNetwork2BIN & "0"
End If

If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
IPNetwork3BIN = IPNetwork3BIN & "1"
Else
IPNetwork3BIN = IPNetwork3BIN & "0"
End If

If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
IPNetwork4BIN = IPNetwork4BIN & "1"
Else
If iCounter = 8 Then
IPNetwork4BIN = IPNetwork4BIN & "1"
Else
IPNetwork4BIN = IPNetwork4BIN & "0"
End If
End If

Next

Case 5 'High IP Dot Dec
BuildBIN = ""
For iCounter = 1 To 32
BuildBIN = BuildBIN & "1"
Else
BuildBIN = BuildBIN & "0"
End If
Next

IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

BuildBIN = ""

For iCounter = 1 To 32
BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
Else
If iCounter = 32 Then
BuildBIN = BuildBIN & "0"
Else
BuildBIN = BuildBIN & "1"
End If
End If
Next

Case 6 'Number of Hosts/Net

iZeroCounter = 0
For iCounter = 1 To 32
iZeroCounter = iZeroCounter + 1
End If
Next

IPSubnetCalc = (2 ^ iZeroCounter) - 2

Case Else 'Incorrect Selection
IPSubnetCalc = "Incorrect Function"
End Select

End Function
``````
-

Did you specifically want a spreadsheet to calculate subnets? There are plenty of standalone tools to do this. Here for example

-
Was the bit where he said "but is in an excel spreadsheet" not clear enough? :P – Izzy Sep 29 '09 at 15:27
a standalone tool is an option, but I record all my network stuff in a spreadsheet and want to have everything to hand in one place. (whether I should be using a spreadsheet is another question - but it works). – Martin Sep 29 '09 at 17:29
The above tool can export a spreadsheet. – JS. Sep 29 '09 at 17:57