Updated 2023-12-11: Hosts calculation updated to support CIDR or 31 and 32.
Prerequisites:
- A1 contains an IP address, such as 10.0.0.2
- B1 contains the number of bits in the netmask (CIDR) such as 24
The below formulas then go into C1, D1 etc. to perform the various calculations. Some calculations depends on other calculations.
A Google Sheet with all the formulas can be accessed and copied here: https://docs.google.com/spreadsheets/d/1G-vStX0DRB7tq-wvbALnL4HeXYXXOkjh_GriTN-b6-o (Also updated 2023-12-11)
Note, the formulas below are written with the comma spreadsheet notation used in most countries globally. You may need to change the notation to semicolon notation, which is primearly used in the U.S. E.g. =ROUND(PI(),3) and =ROUND(PI();3) are identical but uses different formula notation.
Huge thanks to @6d6163 and @Baribf for pointing out a mistake when used in Excel (The instance_num in Substitute() must not be zero!) and for spotting that the StartIP in a range must be one higher than the range number. They also spotted a copy/paste error mixing up A1 and C1. Their work is what triggred the march 16. 2023 update of these formulas.
=BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))
&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))
&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))
&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))
=BITAND(
(LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
(BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8)))
)
&"."&
BITAND(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
(BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)))
)
&"."&
BITAND(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)),
(BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
)
&"."&
BITAND(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)),
(BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))
)+1
=BITOR(
(LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8)))
)
&"."&
BITOR(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)) )
)
&"."&
BITOR(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
)
&"."&
BITOR(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))
)-1
=BITOR(
(LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8)))
)
&"."&
BITOR(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)))
)
&"."&
BITOR(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
)
&"."&
BITOR(
(MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)),
(255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))
)
Updated the method of calculating the number of hosts, to peoperly calculate the number of hosts when CIDR is set to 31 (peer-to-peer network) and 32 which is a single IP.
=switch(B1, 31,2, 32,1, (2^(32-B1)-2))
For an IP setup such as 10.100.10.20/20 the following values will be calculated:
- Netmask: 255.255.240.0
- Start IP: 10.100.0.1
- End IP: 10.100.15.254
- Broadcast IP: 10.100.15.255
- Number of hosts: 4094
I was thrilled to find these formulas as I have a list of IP addresses I need to find the starting and ending IPs. However, like OriCat101 I get the #VALUE! error and testing an IP in the provided Google Sheet seems to show that it still needs some polishing as it doesn't work 100% identifying the proper starting address. Try it with 4.128.0.0/16 and the starting address will be 4.12.0.0 instead of 4.128.0.0 (missing the 8). If you try 4.128.0.0/12 I get 4.0.0.0 as the starting address instead of 4.128.0.0 which is valid.
I don't know if it totally worked for your purpose, but comparing www.calculator.net/ip-subnet-calculator.html it's clear there's a mismatch between the results.
I know that this is not a trivial task to do with just Excel formulas (no vba). Therefore, I commend you for taking the time to make it public and documenting it. You did most of the heavy lifting. If I may you'll find below the updated formulas that returns the same results as the ip-subnet-calculator. I've already spent too much time on this so I won't list the detailed updates but here's 2 least obvious ones: I've replaced the references to C1 by A1 and changed the "instance number" parameter in the SUBSTITUTE function from 0 to 1. I guess the latter is a difference between Google Sheet and MS Excel.
SUBNET MASK
=BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))
START IP
=BITAND( (LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),(BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))))
&"."&
BITAND( (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)))
&"."&
BITAND( (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)), BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
&"."&
BITAND( (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))+1
END IP
=BITOR((LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))))-1
BROADCAST IP
=BITOR((LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))))
Thanks