You may have a list of IP addresses which follow CIDR format, i.e. 111.12.23.45/16 and which you wish to sort. Here is an algorithm to do so in Microsoft Excel:

Step 1. Paste all IP addresses in to a single column A in your Excel sheet.

Step 2. Remove the CIDR part of the IP address in column A by applying the following formula to the first row in Column B:

=LEFT(A1,FIND("/",A1)-1)

This will turn an IP address, such as 111.12.23.45/16 in to 111.12.23.45.

Step 3. Once you get a valid IP address in Column B, double click on the bottom-right corner of the cell to propagate for formula to the rest of the rows in Column B.

Step 4. Select the entire Column B and then paste again using Paste Special… Values. This will remove all the formulas and retain only the IP values.

Step 5. Apply the following formula in Column C in order to make sure each octet of your IP address has three digits:

=TEXT(LEFT(B1,FIND(".",B1,1)-1),"000") & "." & TEXT(MID(B1,FIND( ".",B1,1)+1,FIND(".",B1,FIND(".",B1,1)+1)-FIND(".",B1,1)-1),"000") & "." & TEXT(MID(B1,FIND(".",B1,FIND(".",B1,1)+1)+1,FIND(".",B1, FIND(".",B1,FIND(".",B1,1)+1)+1)-FIND(".",B1,FIND(".",B1,1)+1)-1), "000") & "." & TEXT(RIGHT(B1,LEN(B1)-FIND(".",B1,FIND(".",B1,FIND( ".",B1,1)+1)+1)),"000")

[*] https://www.extendoffice.com/documents/excel/4946-excel-sort-ip-address.html

This will convert all your IPs (without CIDR notation) in to a set of numbers with the following format: XXX.XXX.XXX.XXX. That is to say, each octet will contain exactly three digits. In cases where an octet originally has only two digits, then a zero will be prepended. In the case where the octet originally has only one digit, then two zeros will be prepended.

For example, our original IP: 111.12.23.45/16 will now look like: 111.012.023.045

Step 5. Apply the same thing you did in Step 4. but to this new column C, so that only the values remain.

You should now have three columns in your Excel sheet. The first column is a list of your original IPs in CIDR format. The second is a list of the same IPs without the CIDR notation. The third is a list of the same IPs where each octet has precisely three digits.

Step 6. The final step is to sort the list by column 3: Select the whole sheet by clicking on the top-right corner of the sheet where the rows and columns meet. Go to Menu Item: ‘Data’… click on the ‘Sort’ icon, and select Column 3.

One caveat for using this algorithm:

This algorithm does not take into account the CIDR values when sorting.