88 lines
3.1 KiB
Plaintext
88 lines
3.1 KiB
Plaintext
A1 = IP address
|
|
B1 = CIDR netmask
|
|
|
|
ref https://gist.github.com/f-steff/d2ef30bed5328f0e417d635d3b46e256
|
|
|
|
|
|
Calculate netmask:
|
|
==================
|
|
|
|
=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))
|
|
|
|
|
|
Calculate start of IP range (i.e. 'network' address, FIRST HOST is +1 from this):
|
|
=================================================================================
|
|
|
|
=BITAND(
|
|
(LEFT(A1, find(char(160),SUBSTITUTE(A1,".",CHAR(160)))-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,len(A2)-find(char(160),SUBSTITUTE(A1,".",CHAR(160),3))))
|
|
,
|
|
bin2dec(mid(REPT("1",B1)&rept("0",32-B1),25,8))
|
|
)
|
|
|
|
|
|
Calculate end of IP range (i.e. 'broadcast' address, LAST HOST is -1 from this):
|
|
================================================================================
|
|
|
|
=BITOR(
|
|
(LEFT(A1, find(char(160),SUBSTITUTE(A1,".",CHAR(160)))-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,len(A2)-find(char(160),SUBSTITUTE(A1,".",CHAR(160),3))))
|
|
,
|
|
255-bin2dec(mid(REPT("1",B1)&rept("0",32-B1),25,8))
|
|
)
|
|
|
|
|
|
|
|
Calculate number of hosts:
|
|
==========================
|
|
|
|
=2^(32-B1)-1
|
|
|
|
|
|
===================================================================================================================================
|
|
(Does not follow on from above columns.)
|
|
===================================================================================================================================
|
|
|
|
Convert traditional netmask in A1 to CIDR:
|
|
|
|
="/"&32-LEN(SUBSTITUTE(TEXT(DEC2BIN(MID(A1,1,FIND(".",A1)-1)),"00000000")&TEXT(DEC2BIN(MID(A1,1+FIND(".",A1),FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)),"00000000")&TEXT(DEC2BIN(MID(A1,1+FIND(".",A1,FIND(".",A1)+1),FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-FIND(".",A1,FIND(".",A1)+1)-1)),"00000000")&TEXT(DEC2BIN(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))),"00000000"),"1",""))
|
|
|
|
(ref https://www.reddit.com/r/excel/comments/9ivaal/simple_formula_ip_subnet_masks_to_cidr/)
|