sorting IP/subnet

Hi
I am trying to sort IP's in the following format
10.0.0.0/8
10.0.1.0/16
10.0.0.0/16
10.0.4.0/16
10.0.4.64/27
10.0.4.0/30
I got a formula which works well when the subnet mask i.e. /xx is not appended to the IP

=RIGHT("00"&LEFT(A2,FIND(".",A2)-1),3)&"."&RIGHT("00"&MID(A2,FIND(".",A2)+1,FIND("|",SUBSTITUTE(A2,".","|",2))-FIND(".",A2)-1),3)&"."&RIGHT("00"&MID(A2,FIND("|",SUBSTITUTE(A2,".","|",2))+1,FIND("|",SUBSTITUTE(A2&".",".","|",3))-FIND("|",SUBSTITUTE(A2,".","|",2))-1),3)&"."&RIGHT("00"&RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))),3)

BUT I need the subnet mask and the expected result for the above example will be
10.0.0.0/8
10.0.0.0/16
10.0.1.0/16
10.0.4.0/16
10.0.4.0/30
10.0.4.64/27

Any help is appreciated
Thanks

I am not sure how to add another level of sort in column B as you suggest since when I do that the sorting in column A gets messed up.

Since IP addresses can have values like 10.0.1.20/12 , 10.0.128.0/15 or 10.0.2.0/16 it becomes important to first split subnet in column B , apply the formula
=RIGHT("00"&LEFT(A2,FIND(".",A2)-1),3)&"."&RIGHT("00"&MID(A2,FIND(".",A2)+1,FIND("|",SUBSTITUTE(A2,".","|",2))-FIND(".",A2)-1),3)&"."&RIGHT("00"&MID(A2,FIND("|",SUBSTITUTE(A2,".","|",2))+1,FIND("|",SUBSTITUTE(A2&".",".","|",3))-FIND("|",SUBSTITUTE(A2,".","|",2))-1),3)&"."&RIGHT("00"&RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))),3)
on only the IP that is column A and then use the new column and subnet with the 2 levels of sort.

Thanks for your help ...both techniques combined solved my problem

Hi

newdoverman's solution is clear, efficient and easy to implement.

If, however, for some reason you'd want no auxiliary columns and a dynamic solution (that would auto-update if you change the input IP's) you could use in B1:

=INDEX($A$1:$A$6,MATCH(SMALL(MMULT((MID(SUBSTITUTE(SUBSTITUTE($A$1:$A$6,"/","."),".",REPT(" ",15)),1+{0,1,2,3,4}*15,15))*256^{4,3,2,1,0},{1;1;1;1;1}),ROWS($B$1:B1)),MMULT((MID(SUBSTITUTE(SUBSTITUTE($A$1:$A$6,"/","."),".",REPT(" ",15)),1+{0,1,2,3,4}*15,15))*256^{4,3,2,1,0},{1;1;1;1;1}),0))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

Copy down

Remark: this assumes the source IP's in A1:A6 with valid IP's in the format posted. It could be upgraded to allow for eventual empty cells inside, or other requirements.