Creating 'Network IP' and 'Subnet IP' Ranges from IP CIDR subnets ranges

"Creating 'Network IP' and 'Subnet IP' Ranges from subnets"

After reading the following post: Creating IP Ranges from IP Add (Network) and Subnet Mask , I was trying to modify the calculations (calculations taken from previous post) to get 'Network IP' and the 'Broadcast IP' from IP CIDR subnet ranges.

Unlike 'dan_in-need-of-help' (Topic starter previous post), my approach is to get only 'Network IP' and the 'Broadcast IP' ranges from a subnet, shown as in my example:
When you talk about a CIDR subnet range, then you talk about ranges like:



Subnet: 192.168.0.0/24

That's is equal to:

Network: 192.168.0.0
Netmask: 255.255.255.0

or

Network range 192.168.0.0 - 192.168.0.255



What i trying to achieve is creating 'Network Ranges' from 'Subnets'.
See the example list:


COLUMN 1_______COLUMN 2_______COLUMN 3
Subnet__________Network IP_______Broadcast IP

192.168.10.0/24___192.168.10.0___192.168.0.255
172.29.31.5/32____172.29.31.5____172.29.31.5
10.0.29.128/25____10.0.29.128____10.0.29.255
10.133.11.32/27___10.133.11.32___10.133.11.62
10.50.0.0/18______10.50.0.0______10.50.63.254


For the bitmask notation (/24 or /32) in the subnet, i search that the calculation can handle the following values:

Prefix size______ Network mask______ IP Adresses
/8______ 255.0.0.0 1______ 6777216
/9______ 255.128.0.0______ 8388608
/10______ 255.192.0.0______ 4194304
/11______ 255.224.0.0______ 2097152
/12______ 255.240.0.0______ 1048576
/13______ 255.248.0.0______ 524288
/14______ 255.252.0.0______ 262144
/15______ 255.254.0.0______ 131072
/16______ 255.255.0.0______ 65536
/17______ 255.255.128.0______ 32768
/18______ 255.255.192.0______ 16384
/19______ 255.255.224.0______ 8192
/20______ 255.255.240.0______ 4096
/21______ 255.255.248.0______ 2048
/22______ 255.255.252.0______ 1024
/23______ 255.255.254.0______ 512
/24______ 255.255.255.0______ 256
/25______ 255.255.255.128______ 128
/26______ 255.255.255.192______ 64
/27______ 255.255.255.224______ 32
/28______ 255.255.255.240______ 16
/29______ 255.255.255.248______ 8
/30______ 255.255.255.252______ 4
/31______ 255.255.255.254______ 2
/32______ 255.255.255.255______ 1


If someone have an idea or a sheet to share or can modify the example sheet, it would be more then welcome!