This post will guide you how to sort IP address in your worksheet. How do I sort IP address with an excel formula. How to sort IP address with Text To Columns feature in excel.

## Sort IP Address with Formula

Assuming that you have an IP list of data in the range of cells B1:B5, and you want to sort those IP list from low to high, how to achieve the result with excel formula. You need to create a formula based on the Text Function, the LEFT function, the FIND function and the MID function, the RIGHT function. Just like this:

```=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")```

You will see this formula is quite long, but it is a single one formula and let’s see the detailed steps:

#1 type this formula into the formula box of Cell C1, then press Enter key in your keyboard, and drag the AutoFill Handler over other cells to apply this formula to sort other IP Address. #2 keep the newly IP Address selected, and go to DATA tab, click Sort A to Z under Sort&Filter group. #3 select Expand the selection radio button, and then click Sort button. #4 you will see the IP addresses have been sorted. ## Sort IP Address with Text to Columns

You can also use the Text to Columns feature to sort IP address in excel, You can use this feature to divide the IP Addresses into separate columns and putting each octet in its own column. Before doing those actions, you need to make sure that there are three blank columns next to the right of your IP address. So just do the following steps:

#1 Select the cells that contain IP addresses #2 go to DATA tab, click Text to Columns command under Data Tools group. The Convert Text to Columns Wizard dialog will appear. #3 Checked Delimited option, and click Next button. #4 select the checkbox next to Other: box and then type . into the text box, and click Next button. #5 select one destination cell to paste the result ( C1). Then click Finsh button.  #6 select all cells that contain IP addresses. #7 click Sort command under Sort&Filter group. The Sort dialog will appear.

#8 clicking Add Level to sort data from column C to F.

#9 click Ok button. You will see that the IP addresses have been sorted. ### Related Functions

• Excel Text function
The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
• Excel LEFT function
The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
• Excel FIND function
The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
• Excel RIGHT function
The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
• Excel MID function
The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….

Related Posts

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Change Date to The Day of Week in Excel

In excel, we can type a date properly if set cell format as ‘Date’. If there is a list includes multiple dates, is there any way to convert these dates to the days of week accordingly? Actually, there are some ...

How to Create Increment Number with Texts in Excel

Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts ...

How to Generate Random Time in Excel

In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend ...

How to Combine Text and Date into one Cell in Excel

This post will guide you how to combine text and dates in the same cell in Excel. How do I combine text and date values into one Cell in Excel 2013/2016. Combine Text and Date into One Cell Assuming that ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Convert Military Time to Standard Time in Excel

This post will guide you how to convert military time to standard time with a formula in Excel. How do I convert from military time 17:29:30 to regular time 5:29:30PM in Excel.  How do I Convert Standard time to military ...

How to Delete or Remove Year from a Date in Excel

This post will guide you how to delete or remove year from a given standard date in Excel. How do I remove the year from a date with a formula in Excel. Remove Year from a Date Assuming that you ...

How to Convert Date to YYYY-MM-DD format in Excel

This post will guide you how to convert the current date to a specified date format in Excel. How do I convert date to YYYY-MM-DD format with Format Cells Feature in Excel. How to convert date format to a specific ...

Sidebar