Written by Allen Wyatt (last updated January 28, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

Chuck has a worksheet that, in one column, contains a series of IP addresses. These are in the familiar format of 192.168.2.1. If he sorts the addresses, they are not numerically sorted. For instance, Excel places 192.168.1.100 between 192.168.1.1 and 192.168.1.2. Chuck wonders if there is a way to sort a column of IP addresses so they appear in the proper sequence.

This happens because Excel views an IP address as text, not as a number or a series of numbers. There are a few ways you can work around the problem, a few of which I'll discuss in this tip. You should choose the approach that is right for your needs, as defined by your data and how you use that data.

One approach is to make sure that each octet of your IP addresses consist of three digits. (An octet is each part of the IP address, separated by periods.) For instance, instead of an address such as 192.168.1.1, you would use 192.168.001.001. This "front pads" each octet with zeros and, if all of your IP addresses are in this format, they will sort correctly.

If you prefer to use a formula to ensure the front-padding of each octet, you could use the following:

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

This formula is quite long, but it is still a single formula. Place it in the column next to your first IP addresss (assuming that address is in cell A1) and then copy it down as many rows as required. When you do your sorting, sort by column B, and the addresses will be in the proper sequence.

If you work with a lot of IP addresses, you may want to create a user-defined function that will front-pad each octet of the IP address with zeros and then return a fully formatted IP. The following will perform the task:

```Function FormatIP(IPAddr As String) As String
Dim Dot1 As Integer
Dim Dot2 As Integer
Dim Dot3 As Integer
Dim Octet1 As String
Dim Octet2 As String
Dim Octet3 As String
Dim Octet4 As String

Dot1 = InStr(1, IPAddr, ".", vbTextCompare)
Dot2 = InStr(Dot1 + 1, IPAddr, ".", vbTextCompare)
Dot3 = InStr(Dot2 + 1, IPAddr, ".", vbTextCompare)

FormatIP = Right("000" & Octet1, 3) & "."
FormatIP = FormatIP & Right("000" & Octet2, 3) & "."
FormatIP = FormatIP & Right("000" & Octet3, 3) & "."
FormatIP = FormatIP & Right("000" & Octet4, 3)
End Function
```

In Excel, then, you could use the UDF in this manner, assuming your original IP address in in cell A1:

```=FormatIP(A1)
```

Another approach is to simply divide the IP addresses into separate columns, putting each octet in its own column. This is easy to do if you use the Text to Columns tool, in this manner:

1. Make sure there are three blank columns to the right of your IP addresses.
2. Select the range of cells containing the IP addresses.
3. Display the Data tab of the ribbon.
4. Click the Text to Columns tool, in the Data Tools group. Excel starts the Convert Text to Columns Wizard. (See Figure 1.)
5. Figure 1. The beginning of the Convert Text to Columns Wizard.

6. Choose the Delimited radio button.
7. Click on Next.
8. Select the checkbox next to Other and, to the right of the check box, enter a period.
9. Click on Finish.

Once done, you can sort the four columns as you would normally sort numbers. Then, when you want to put the IP addresses back together, you could use a formula such as this:

```=A1 & "." & B1 & "." & C1 & "." & D1
```

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13481) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding the For ... Next Structure

Spend any time creating Word macros, and sooner or later you will need to repeat some of your programming code a certain ...

Discover More

Saving Information in a Text File

The VBA programming language provide with Excel allows you to create and modify text files quite easily. Here's how to ...

Discover More

There are three different ways that Excel allows you to display any comments that are in your worksheet. Here's how you ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Incomplete and Corrupt Sorting

Using the sorting tools, on the toolbar, may result in some unwanted results, such as jumbled data. If this happens to ...

Discover More

Too Many Formats when Sorting

Sorting is one of the basic operations done in a worksheet. If your sorting won't work and you instead get an error ...

Discover More

Want to ignore some characters at the beginning of each cell when sorting? The easiest way is to simply create other ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two less than 9?

2021-02-09 15:57:39

Patrick.Burwell

After separating IPV4Addresses, with "Text to column" delimiter by ".", Sort by Column A, then B, then C, etc.
Then reorg with...
=concat(A1,".",B1,".",C1,".",D1)
NOTE: To get the /24 off the end of an address with the mask, like 10.1.1.0/24, you have to delimit again by "/" and then the process becomes two-step with an extra step to add the forward slash (/) back after the prior step, all to another column. Then copy and paste to a column as text, to preserve as text.
Note: All the other options with text formula are very long BS.
-Have fun!

2020-10-30 10:18:45

sunny bafna

Thank you very much it helped !!! Kudos.

2020-09-15 11:29:13

Roy

A different approach to the helper column would be:

=TEXTJOIN("",FALSE,TEXT(FILTERXML("<Group><Members>"&SUBSTITUTE(C1,".","</Members><Members>")&"</Members></Group>","//Group/Members"),"000"))

It uses the FILTERXML() approach for splitting data (that takes up most the line as the "tags" I chose for clarity are not single characters meant to shorten it). Then TEXT() to achieve the three character sets for sorting. Finally, TEXTJOIN() puts it all back together in a single cell.

It will handle any length string that is divided by the "." so IPV4 and IPV6 both come out just fine.

It is text but that doesn't matter as the pieces were padded to three characters so an "alphabetical" (or "A-Z") type sort works perfectly. And the padding, and not putting the "." characters back in, doesn't matter because it's the original column that is "human readable" to borrow from the barcoding world.

Sorting dates using "YYYYMMDD" works perfectly alphabetically as well for the same kind of reason as here.

However, the above approach does not lend itself to SPILL functionality as TEXTJOIN() puts EVERYTHING together so a range inside the SUBSTITUTE() will get one (very) long string, not a column of values to sort on.

2020-03-11 12:58:57

Scott

Here's the simplest formula I've come across that works well. This creates a simple numeric value in separate column that you can sort on. No need to modify the IP address in any way.
Assuming your IP Address is in Cell A2, Use this formula in a separate column and sort on it.

=VALUE( LEFT(SUBSTITUTE(A2, ".", " "), 3 ))*2^24
+ VALUE( MID(SUBSTITUTE(A2, ".", " "), 8, 5 ))*2^16
+ VALUE( MID(SUBSTITUTE(A2, ".", " "), 15, 7))*2^8
+ VALUE(RIGHT(SUBSTITUTE(A2, ".", " "), 3 ))

2019-09-28 20:17:47

Peter Atherton

Concatenate
Excel 2010 brought in the TEXTJOIN function that makes joining cells values easy. The syntaxt is TEXTJOIN(Delimiter, Ignore_empty, range)
So the expression =D2&"."&E2&"."&F2&"."&G2 could be rewritten as =TEXTJOIN(".",True,D2:G2)
However, if you have the Home & Student version of Office, you will not have this function available. Here is a VB Version, it is not as versatile as the Microsoft function but it has the option to join the cells in reverse order; I was playing around with binary numbers as the time so I added this option.

Function VBTEXTJOIN(ByVal ref As Range, _
Optional separator As String = ", ", _
Optional include_blanks As Boolean = False, _
Optional Order As Integer = 0) As String

Dim c As Range, str As String, x As Integer

x = Len(separator)
Select Case Order
Case Is = 0
For Each c In ref
If include_blanks = False And Len(c) > 0 Then
str = str & c & separator
ElseIf include_blanks Then
str = str & c & separator
End If
Next c
GoTo ReturnJoin
Case Is > 0
For Each c In ref
If include_blanks = False And Len(c) > 0 Then
str = c & separator & str
ElseIf include_blanks Then
str = c & separator & str
End If
Next
End Select
ReturnJoin:
VBTEXTJOIN = Left(str, Len(str) - x)
End Function

So to concatenate D2:G2 you would enter VBTEXTJOIN(D2:G2,".")

2019-09-28 12:58:29

Willy Vanhaelen

@Bill A
You even don't need CONCATENATE. This is shorter:

=D2&"."&E2&"."&F2&"."&G2

2019-09-27 12:37:11

Bill A

Padding with 0's is a terrible idea! If you do a lookup on an address with padded 0's, it is converted to the hex equivalent.

@Jason, You don't need Notepad++ . Excel has a nice feature called CONCATENATE

=CONCATENATE(D2,".",E2,".",F2,".",G2)

2019-08-24 00:33:07

Lon

Allen, this was super helpful, thanks for all the details. The Macro worked perfectly.

2017-07-10 18:42:36

Jason

Here's my solution (uses Excel and Notepad++):

Paste your list of IP addresses on line 2 in any column.
Select the column.
Go to the Data tab.
Click Text to Columns.
Click Delimited.
Click Next.
Check "Others" and enter a period.
Click Finish.

Highlight those four columns.
Go to the Home tab.
Click Sort & Filter.
Click Filter.
Now line 1 will have a drop-down sort menu for each column.
Start at the 4th column, click the drop-down menu. Sort Smallest to Largest
Repeat for the 3rd column, then the 2nd, then the first.

Highlight all of the IP addresses and copy.
Double click on the space between two numbers (where a period should go)
Press CTRL F
Click the Replace tab
"Find what" should be filled with the space you double clicked.
Click "Replace with" and type in a period.
Click Replace All.

In Excel, you can disable Text to Columns by highlighting a column, Clicking Text to Columns in the Data tab, click Next with Delimiter marked, uncheck all delimiter boxes and pressing Finish.

Now you can safely paste the sorted IP addresses back into Excel.

Once you get the hang of it you can sort any number of IPs in about 30 seconds.
This could be faster with a way to recombine the IPs within Excel, but I don't know how.

2017-03-28 09:58:21

MIchael Armstrong

What was @debbie's solution? I often see references to comments that aren't there. Wazzup with that?

2017-03-27 16:47:20

Justin Grote

As a minor update, if you use excel tables, you can just reference a column name. The below formula will work as long as your IP address column is named "IPv4" but of course you can update it however you want.

Easier than having to edit it every time if your IP addresses aren't in the "A" column

=TEXT(MID([@IPv4],1,FIND(".",[@IPv4])),"000")&"."&TEXT(MID([@IPv4],FIND(".",[@IPv4])+1,FIND(".",[@IPv4],FIND(".",[@IPv4])+1)-1-FIND(".",[@IPv4])),"000")&"."&TEXT(MID([@IPv4],FIND(".",[@IPv4],FIND(".",[@IPv4])+1)+1,FIND(".",[@IPv4],FIND(".",[@IPv4],FIND(".",[@IPv4])+1)+1)-1-FIND(".",[@IPv4],FIND(".",[@IPv4])+1)),"000")&"."&TEXT(MID([@IPv4],FIND(".",[@IPv4],FIND(".",[@IPv4],FIND(".",[@IPv4])+1)+1)+1,LEN([@IPv4])),"000")

2016-10-17 08:51:45

debbie

my solution was the easiest of all the comments you have there.

2016-10-17 06:18:01

Willy Vanhaelen

@Micky

I tried but it crashes because I always use the Option Explicit statement. So I had to add an 8th line "Dim P As Integer" -:)

But you can even go for 6 lines if you delete "Dim Ar As Variant" but then you must also delete the Option Explicit statement which is not recommended and all you undeclared variables are then Variants.

2016-10-16 10:10:42

Michael (Micky) Avidan

@Willy Vanhaele,
Try "tiny" 7 Lines:
--------------------------------
Function FormatIP(IPAddr As String) As String
Dim Ar As Variant
For P = 0 To 3
Ar(P) = Right(Ar(P) + 1000, 3)
Next
FormatIP = Join(Ar, ".")
End Function
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL

2016-10-15 05:51:42

Willy Vanhaelen

The 20 lines macro of this tip can be reduced to this tiny 8 lines one:

Function FormatIP(IPAddr As String) As String
Dim Ar As Variant
Ar(0) = Right(Ar(0) + 1000, 3)
Ar(1) = Right(Ar(1) + 1000, 3)
Ar(2) = Right(Ar(2) + 1000, 3)
Ar(3) = Right(Ar(3) + 1000, 3)
FormatIP = Join(Ar, ".")
End Function

Simplicity is the ultimate sofistication (Leonardo du Vinci).

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.