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
Thanks burger87.
Depends on what you expect.
It's impossible to perform any of the calculations with a CIDR of 32, as that only specifies a single IP and not a network range. Likewise, a CIDR of 31 specifies a (rarely used) peer-to-peer network and also not a network range. In both cases, I guess each formula could be expanded to perform a check of the CIDR value and write "invalid" instead of the current wrong IP address. I've chosen not to do so in order not to clutter the formulas more than they already are.
I did update the hosts calculation, as that was very obviously incorrect for CIDR of 31 and 32.
Finally, I added a summary with examples of the above to the shared spreadsheet.
For now, these are just formulas to calculate the various IP's in an IP range, but you are free to improve on any area you feel needs improvement, include it in your projects, and share it back with all of us.
I'm not sure what you mean by "highlight any duplicate IP or range?", but if you try to explain further I might give it some thought.