Sorting IP Addresses

Thanks to J Woolley.

I normally try to only use functions etc. that are in the basic 2010 Excel version
There are now so many different versions - year numbers, .com. and Basic, home, Pro, Pro-Plus,
as well as the facilities that are accessible with E1, 3, 5 licences and canary versions

I'll be looking at the facilities in your toolbox - when I get my sandbox system running.


And I note the (2016) comment re. using
Ar(P) = Right(Ar(P) + 1000, 3)

From
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)

If the IP addresses are in column A, jamies' formula
    =CONCAT(TEXT(TEXTSPLIT(A1, "."), "000"))
is excellent if copied down column B. Then column A can be sorted by column B (Alt+A+SS). Or the following dynamic array formula in cell C1 will return column A sorted by column B:
    =SORTBY(A:A, B:B)
But CONCAT requires Excel 2019, TEXTSPLIT currently requires Excel 365, and SORTBY requires Excel 2021.
Using functions available in My Excel Toolbox, jamies' formula can be replaced by the following:
    =JoinAsText("", TRUE, TEXT(SplitText(A1, "."), "000"))
And this array formula in cell C1 will return column A sorted by column B:
    =GetCols(SortPlus(A:B, 2), 1)
When using pre-2021 versions of Excel without support for dynamic arrays, review the PDF file UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox/

=CONCAT(TEXT(TEXTSPLIT(A1,"."),"000"))
(OK using the new function)

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!

Thank you very much it helped !!! Kudos.

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.

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

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,".")

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

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

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)

Puts your IP address back together

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

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.

Now your IP addresses will be split across four columns.
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.

All of your IP addresses are now sorted.
Highlight all of the IP addresses and copy.
Paste into Notepad++
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.

Your sorted IP addresses are now recombined.

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.

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

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

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

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

@Willy Vanhaele,
Try "tiny" 7 Lines:
--------------------------------
Function FormatIP(IPAddr As String) As String
Dim Ar As Variant
Ar = Split(IPAddr, ".")
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

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 = Split(IPAddr, ".")
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).