How to Quickly sort IP Address from Low to High in Excel?

Sorting IP addresses from low to high in Excel can be useful for the below-provided reasons:

• It is useful to identify the network ranges. It allows the identification of the contiguous network ranges.

• It helps in detecting duplicate records. This ultimately allows the user to resolve the potential conflicts in network configurations.

• It allows the user to enhance the readability of the stored data.

• Calculating the number of addresses in a particular range or subnet becomes easy.

• Filtering data becomes easy.

This article contains an illustration of the process of sorting the IP address from low to high in Excel by using the user-defined formula. The provided formula is lengthy but will completely align with the task requirements.

Example 1: To quickly sort the IP address from low to high in Excel by using the user-defined formula.

Step 1

To understand the process of sorting an IP address, consider a few record values for IP data. In the available sheet create the first column for IP address, and second column for formatted values. Snapshot for the same is provided below:

Step 2

After that user needs to first remove extra zero, available in the number, same is useful to create a new number effectively. To do so go to the D3 cell, and type formula “=VALUE(TRIM(MID(C3, 1, SEARCH(".", C3) - 1))) & "." & VALUE(TRIM(MID(C3, SEARCH(".", C3) + 1, SEARCH(".", C3, SEARCH(".", C3) + 1) - SEARCH(".", C3) - 1))) & "." & VALUE(TRIM(MID(C3, SEARCH(".", C3, SEARCH(".", C3) + 1) + 1, SEARCH(".", C3, SEARCH(".", C3, SEARCH(".", C3) + 1) + 1) - SEARCH(".", C3, SEARCH(".", C3) + 1) - 1))) & "." & VALUE(TRIM(MID(C3, SEARCH(".", C3, SEARCH(".", C3, SEARCH(".", C3) + 1) + 1) + 1, LEN(C3))))”. This formula will convert the number into the required formatted value, by removing extra zero added to the number. A snapshot for reference is provided below:

The Explanation for Formula

The above-provided formula is lengthy. It is used to extract the individual octets (segments) of an IP address, by converting the values to numeric values, and then concatenating them back together with dots to form a new string representing the IP address in a sortable format.

• MID(C3, 1, SEARCH(".", C3) - 1): This part of the formula, allows the user to extract the first octet of the IP address from cell C3. By using the SEARCH function to determine the position of the first dot ".", and then the MID function extracts the characters from the beginning of the IP address up to the dot.

• VALUE(TRIM(...)): The TRIM function is used to remove all the leading or trailing spaces in the extracted octet.

• & "." &: This part of the formula is used to concatenate a dot "." after the first octet to separate it from the next octet.

Step 3

After that drag the fill handle to copy the same formula for other available rows. Obtained results are provided below.

Step 4

Now, since the data is converted into a string format. This simply means that the user needs to sort the data, to obtain the required result. To do so, click on the “Data” tab, and then click on the “Sort & Filter” option. In the appeared option list select the first option, “A Z”. This option will allow the user to sort data in ascending order. A snapshot of the same is provided below:

Step 5

This step will open the “Sort Warning” dialog box. After that select the first option “Expand the selection” and click on the “Sort” button. A snapshot of the same is provided below:

Step 6

The sorted data snapshot is attached below for user reference:

Conclusion

This article is useful when a user wants to sort the available IP address. In this article, user will understand the process of fixing the structure of provided IP address, and after that sorting the available values, to ensure that the user will be able to generate the required output.