Converting CIDR into IP Ranges
In updating ProjectBASS.org IP address to Network Owner, I frequently encounter raw data where the IP addresses are expressed in terms of CIDR notation. Unfortunately, I need to get the starting IP and ending IP from this CIDR information.
For example: we might see one of Globe Telecom’s IP address block expressed as: 180.190.237.0/24. This needs to be converted into the starting IP address and Ending IP address — in decimal form. In short, we need to convert CIDR notation into an IP range: 180.190.237 and 180.190.255
Using Excel, let me show you how it can be done:
First, we create a subnet lookup table, given the /CIDR notation:
Second, we break the IP address in CIDR notation into the Starting IP and CIDR:
On B2 we enter this formula:
=LEFT(A2,FIND(“/”,A2)-1)
On C2 we enter this formula:
=RIGHT(A2,LEN(A2)-FIND(“/”,A2))
Essentially we look for the [“/”] character in the A2 string value and split it into the IP block (B2) and CIDR value (C2).
Third comes the hard part:
Finding the ENDING IP address for the CIDR notation. And here is where we use the Substitute() function to find the nth occurence of [“.”] and use it to split up the IP address into 4 octets:
For example A2 whose value is 1.37.80.20/24 is split into 4 octets from cell D2 to G2 as follows: 1, 37,80,0
On Cell D2, here is the formula:
=MID(A2,1,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,1))-1)
On Cell E2, here is the formula:
=MID(A2,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,1))+1,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,2))-FIND(“~”,SUBSTITUTE(A2,”.”,”~”,1)) -1 )
On Cell F2, here is the formula:
=MID(A2,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,2))+1,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,3))-FIND(“~”,SUBSTITUTE(A2,”.”,”~”,2)) -1 )
Finally, on Cell G2, here is the formula:
=MID(A2,FIND(“~”,SUBSTITUTE(A2,”.”,”~”,3))+1,LEN(A2)-FIND(“/”,A2)-1)
After we break the starting IP into 4 fields - one for each octet — we can then use the lookup table to compute the ending IP based off of this:
On cell I2, it uses the CIDR on cell C2 (24) to lookup the subnet value for the octet in the lookup table. So the [24] has 255 for the 1st octet. We use this as the basis for computing the ending 1st Octet in Cell I2.
=D2+255-VALUE(VLOOKUP(VALUE(C2),$Q$2:$U$33,2))
And for H2,J2,K2,L2 we have the following formulas:
=E2+255-VALUE(VLOOKUP(VALUE(C2),$Q$2:$U$33,3))
=F2+255-VALUE(VLOOKUP(VALUE(C2),$Q$2:$U$33,4))
=G2+255-VLOOKUP(VALUE(C2),$Q$2:$U$33,5)
Once we have all the 4 octets of the ending IP, it is trivial to combine them using TEXT() function for the ending IP at cell N2.
=TEXT(I2,”0")&”.”&TEXT(J2,”0")&”.”&TEXT(K2,”0")&”.”&TEXT(L2,”0")
Check out related stories:
Decimal IP to Octet
https://wilson-chua.medium.com/converting-ip-decimal-to-ip-addresses-in-octet-form-a96143c7d021
Octet to Decimal
https://wilson-chua.medium.com/converting-ip-addresses-to-decimal-ip-6ef08905a54e