Sorting IP Addresses in Excel?


G

Guest

I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
(custom format) the numbers to end in three digits to get it to sort properly.

For example, if I sort as-is, I get:

192.103.179.1
192.103.179.10
192.103.179.100
192.103.179.11
192.103.179.110
192.103.179.12

What I'd like to get is:

192.103.179.1 (or .001)
192.103.179.10 (or .010)
192.103.179.11 (or .011)
192.103.179.12 (or .012)
192.103.179.100
192.103.179.110

Any suggestions?
 
Ad

Advertisements

G

Guest

Select the cells, use Data/Text to Columns, select Delimited in step 1,
select "Other" checkbox and enter a period, then click finish. You can then
sort by collumn D. Then you can put the cells together again with a formula
in E1:
=A1&"."&B1&"."&C1&"."&D1 then fill down, then copy E1:E6 (in this example),
Edit/Paste special values, then delete A1:D6 (shift left).
HTH
 
J

Jim Cone

An alternative is the commercial Excel add-in "Special Sort" from yours truly...
http://www.officeletter.com/blink/specialsort.html
--
Jim Cone
San Francisco, USA



"Kris" <[email protected]>
wrote in message
I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
(custom format) the numbers to end in three digits to get it to sort properly.

For example, if I sort as-is, I get:

192.103.179.1
192.103.179.10
192.103.179.100
192.103.179.11
192.103.179.110
192.103.179.12

What I'd like to get is:

192.103.179.1 (or .001)
192.103.179.10 (or .010)
192.103.179.11 (or .011)
192.103.179.12 (or .012)
192.103.179.100
192.103.179.110

Any suggestions?
 
R

Ron Rosenfeld

I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
(custom format) the numbers to end in three digits to get it to sort properly.

For example, if I sort as-is, I get:

192.103.179.1
192.103.179.10
192.103.179.100
192.103.179.11
192.103.179.110
192.103.179.12

What I'd like to get is:

192.103.179.1 (or .001)
192.103.179.10 (or .010)
192.103.179.11 (or .011)
192.103.179.12 (or .012)
192.103.179.100
192.103.179.110

Any suggestions?
Try the macro below. It should sort the IP addresses if they are in a vertical
array.

To enter it, <alt><F11> opens the VB Editor.
Ensure your project is selected in the project explorer window, then
Insert/Module and paste the code below into the window that opens..

To use it, select either a single cell in the range, or a contiguous range of
cells you wish to sort. Then <alt><F8> opens the Macro Dialog box. Select
SortIP and RUN.

I will be away for a few weeks so hopefully this will work for you without
further intervention :)).

=============================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long

IPaddress = "#*.#*.#*.#*"

Set RangeToSort = Selection

'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If

'Check if row 1 contains an IP address. If not, it is a header row

'first find column with IP addresses. Check row 2 since row 1 might be a
header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
If IPColumn > RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
IPColumn = IPColumn + 1
Loop

If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If



'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)



For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j

Next i

rg = BubbleSort(rg, 0)

For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i

End Sub
'-------------------------------------------
Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean

k = UBound(TempArray, 2)
ReDim temp(0, k)

Do
NoExchanges = True

For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) > TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges

BubbleSort = TempArray

End Function

===================================================
--ron
 
Joined
Apr 29, 2010
Messages
1
Reaction score
0
Here is a great link

This site has a great formula to convert an IP Address to a number that you can then sort.

http://www.mvps.org/dmcritchie/excel/sorttcp.htm#tcpn

This is the actual formula:

=((VALUE(LEFT(B1, FIND(".", B1)-1)))*256^3)+((VALUE(MID(B1, FIND(".", B1)+1, FIND(".", B1, FIND(".", B1)+1)-FIND(".", B1)-1)))*256^2)+((VALUE(MID(B1, FIND(".", B1, FIND(".", B1)+1)+1, FIND(".", B1, FIND(".", B1, FIND(".", B1)+1)+1)-FIND(".", B1, FIND(".", B1)+1)-1)))*256)+(VALUE(RIGHT(B1, LEN(B1)-FIND(".", B1, FIND(".", B1, FIND(".", B1)+1)+1))))

Hope you find it useful.
 
Joined
Oct 21, 2011
Messages
1
Reaction score
0
On Mon, 1 May 2006 12:46:02 -0700, Kris
Try the macro below. It should sort the IP addresses if they are in a vertical
array.
[snipp]
Code:
'Check if row 1 contains an IP address.  If not, it is a header row

[COLOR=RED]
'first find column with IP addresses.  Check row 2 since row 1 might be a
header
[/COLOR]
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
--ron
Hi,
Talk about writing to acient posts! :)

The macro presented over is a very usefull macro, and i use it alot. First off i just want to thank you for sharing it!

And second, if there is anyone having problems getting it work, the code got an unfortunate line break, most likly because the forum don't like long lines :) (Marked in red in the quote). It kinda breaks the macro.

Remove or change the line so that it's commented out.

Code:
' First find column with IP addresses.
' Check row 2 since row 1 might be a header
 
Ad

Advertisements

Joined
Jun 29, 2012
Messages
1
Reaction score
0
On Mon, 1 May 2006 12:46:02 -0700, Kris <[email protected]> wrote:

>I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
>(custom format) the numbers to end in three digits to get it to sort properly.
>
>For example, if I sort as-is, I get:
>
>192.103.179.1
>192.103.179.10
>192.103.179.100
>192.103.179.11
>192.103.179.110
>192.103.179.12
>
>What I'd like to get is:
>
>192.103.179.1 (or .001)
>192.103.179.10 (or .010)
>192.103.179.11 (or .011)
>192.103.179.12 (or .012)
>192.103.179.100
>192.103.179.110
>
>Any suggestions?
>
>
>

Try the macro below. It should sort the IP addresses if they are in a vertical
array.

To enter it, <alt><F11> opens the VB Editor.
Ensure your project is selected in the project explorer window, then
Insert/Module and paste the code below into the window that opens..

To use it, select either a single cell in the range, or a contiguous range of
cells you wish to sort. Then <alt><F8> opens the Macro Dialog box. Select
SortIP and RUN.

I will be away for a few weeks so hopefully this will work for you without
further intervention :)).

=============================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long

IPaddress = "#*.#*.#*.#*"

Set RangeToSort = Selection

'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If

'Check if row 1 contains an IP address. If not, it is a header row

'first find column with IP addresses. Check row 2 since row 1 might be a
header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
If IPColumn > RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
IPColumn = IPColumn + 1
Loop

If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If



'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)



For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j

Next i

rg = BubbleSort(rg, 0)

For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i

End Sub
'-------------------------------------------
Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean

k = UBound(TempArray, 2)
ReDim temp(0, k)

Do
NoExchanges = True

For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) > TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges

BubbleSort = TempArray

End Function

===================================================
--ron
I know this is an old post, but I am having issues with this code sorting a 10.0.0.0 subnet of addresses. It works just fine on 192.168.0.0 type addresses. Anyone have any ideas?

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top