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/)