Sorting IP Address

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.

sorting ip address1

#2 keep the newly IP Address selected, and go to DATA tab, click Sort A to Z under Sort&Filter group.

sorting ip address2

#3 select Expand the selection radio button, and then click Sort button.

sorting ip address3

#4 you will see the IP addresses have been sorted.

sorting ip address4

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

sorting ip address5

#2 go to DATA tab, click Text to Columns command under Data Tools group. The Convert Text to Columns Wizard dialog will appear.

sorting ip address6

#3 Checked Delimited option, and click Next button.

sorting ip address7

#4 select the checkbox next to Other: box and then type . into the text box, and click Next button.

sorting ip address8

#5 select one destination cell to paste the result ( C1). Then click Finsh button.

sorting ip address9

sorting ip address10

#6 select all cells that contain IP addresses.

sorting ip address11

#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.

sorting ip address12

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
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
How to Change Date to The Day of Week 6

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
Create Increment Number with Texts 11

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
Generate Random Time 7

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
combine text and data into one cell2

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
highlight cell or row if date8

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
convert military time to standard5

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
delete year from date2

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
convert date format yyyymmdd5

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 ...

How to Concatenate Cells and keeping Date Format in Excel
concatenate cells and keep date format1

This post will guide you how to concatenate cells and keeping data format in Excel. How do I concatenate cells and retain the certain Date format with a formula in Excel 2010/2013/2016. Concatenate Cells and Keeping Date Format Assuming that ...

Sidebar