Sorting TCP/IP Addresses, and the like

Home page:
[View without Frames]

This page contains or refers to some text pages containing VBA macros and User Defined Functions.  If you need assistance to install or to use a macro or function please refer to my  «Getting Started with Macros« or delve into it deeper on my Install  page.

Sorting strings with alpha on left and digits on right (#alphaprefix)

Frequently you have a column of codes that have 1 or more alpha characters followed by numeric digits.  the ALPHA_N function will create a string that can be sorted.  The new column should be formatted as TEXT.  The example here is formatted as 8 characters.l

Orig. String New string Formula
0 00000000 =alpha_N(B2,8)
1 00000001 =alpha_N(B3,8)
A2 A0000002 =alpha_N(B4,8)
A11 A0000011 =alpha_N(B5,8)
A21 A0000021 =alpha_N(B6,8)
A31 A0000031 =alpha_N(B7,8)
AAA104 AAA00104 =alpha_N(B8,8)
AB3 AB000003 =alpha_N(B9,8)

The ALPHA_N functions described above and other macros and functions described on this page can be viewed/downloaded here code/sorttcp.txt

Sorting TCP/IP Addresses (#tcpsort)

Sorting in Excel is limited to 3 columns when invoked; and, while it is possible to sort more columns by invoking the sort more than once, for most purposes this is not very practical.

     TCP/IP number    Reformated for sorting

The subroutines and functions that reformat TCP/IP addresses for sorting or return them to their normalized addressing can viewed/downloaded here code/sorttcp.txt -- Myrna Larson posted enhanced code for Excel 2000 and up using Split and Join VBA functions.

These subroutines or the method be applied to outline numbering systems -- 1, 1.1, 1.11, 1.111, 1.112, 1.113 -- but have since created a ChaptSort macro shown later, which should work better for outline numbering.

IPSORT and IPNORM are functions used like Worksheet Functions.
The example below shows how the F column is expanded with IPSort, or contracted with IPNorm.  The disadvantage is that you are using two columns.  You can see these functions in the User Defined Functions in the Paste Formula Wizard (Shift + F3) the toolbar button looks like [fx].  Select a cell, shift+F3, you will see an equal sign on the formula bar, enter a cellname into the dialog box.

 EFG =personal.xls!IPSort(E20) =personal.xls!IPNorm(E21)
22001.001.001.0011.1.1.1 =personal.xls!IPNorm(E22)

Subroutines: IPSortSUB, IPNormSUB, IP2Text for European usage can be used to change the values inplace without using a secondary cell.  Assuming you installed these functions and macros.  Select cells e20:e22 then run a macro that you find with  ALT+F8  Functions do not show there

More information on finding Functions and Subroutines in my
    Show FORMULA of another cell in Excel
which includes information on installing Functions and Subroutines.

TCP number represented in Decimal (#tcpn)

 1  2054913149 
 2  2054913149
If you don't care if you can read the number you sort on you can convert the TCP/IP number to a decimal number.  Worksheet formulas courtesy of Matt Schuster.

IPv4 Address to Decimal Integer (IPv4 Address is in cell B1)
=((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))))

Decimal Integer to IPv4 Address (Decimal Integer is in cell B2)
=IF(B2<>"", CONCATENATE(INT(B2/256^3), ".", INT(MOD(B2, (256^3))/(256^2)), ".", INT(MOD(MOD(B2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(B2, 256^3), 256^2), 256)), "")

Adding +1 to an IP Address (#tcpplus)

The following works by replacing the period with an "x" adding one and resubstituting the "x" for a period again. (Chip Pearson, 2000-08-15)  

Sorting on Chapter - unknown number of nodes (#chaptsort)

The following code allows sorting of an unknow number of nodes.  A text sort will accomplish the sorting. Code below can also be found in viewed/downloaded from code/sorttcp.txt
Function ChaptSort(cell As String) As String
  'dmcritchie, worksheet.functions, 2004-01-21, _
  '     #sAGbiE4DHA.2388@TK2MSFTNGP09.phx.gbl
   Dim i As Long, j As Long, n As Long
   Dim oldstr As String, newstr As String
   oldstr = cell
   i = 1
   newstr = ""
   j = InStr(Mid(oldstr, i), ".")
   If j > 5 Then
     ChaptSort = "#segment"
     Exit Function
   ElseIf j <> 0 Then
     l = j - l
     newstr = newstr & "." & Left("0000", 5 - j) _
         & Mid(oldstr, i, j - 1)
     i = i + j
     GoTo reloop
     If Len(oldstr) - i >= 4 Then
        ChaptSort = "#length"
        Exit Function
       newstr = newstr & "." & Left("0000",  _
          3 - (Len(oldstr) - i)) & Mid(oldstr, i)
     End If
   End If
   ChaptSort = "*" & Mid(newstr, 2)
End Function
 0001  *0001
 1.2.34567 #length
 1234567  #length
 00010002  #length
 000100020001  #length
 abcde #length
 abcdef #length
 1.abcde #length
 1.1  *0001.0001
 1.1.2 *0001.0001.0002
 1.2.3456.789 *0001.0002.3456.0789
 1.2.3456.7890.1 *0001.0002.3456.7890.0001
 1.3  *0001.0003
 1.abcd *0001.abcd

A more generalized version (#normdigits)

In this more generalized function, the numbers will be normalized at 3 digits, which is also the default.  All non digits will be passed through directly.

NormDigits   Example can also be used for TCP/IP addresses  =personal.xls!NormDigits(A1)  =personal.xls!NormDigits(A2)

Function NormDigits(cell As String, Optional p As Long) As String
  'dmcritchie, worksheet.functions, 2004-01-21, _
  '     #sAGbiE4DHA.2388@TK2MSFTNGP09.phx.gbl
  'dmcritchie, newuser,  2004-07-01
   Dim i As Long, n As String, s As String
   Dim newstr As String
   s = UCase(Trim(cell))
   If p = 0 Then p = 3
   newstr = ""
   n = ""
   For i = 1 To Len(s)
     If Mid(s, i, 1) Like "[0-9]" Then
       n = n & Mid(s, i, 1)
     ElseIf n = "" Then
       newstr = newstr & Mid(s, i, 1)
       newstr = newstr & Format(n, Left("0000000", p))
       n = ""
       newstr = newstr & Mid(s, i, 1)
     End If
  Next i
  If n <> "" Then newstr = newstr & Format(n, Left("0000000", p))
  NormDigits = newstr
End Function

Sorting on Chemical Nomenclature   (#ChemNDigits)

ChemNDigits   Example with Chemical Nomenclature

The numbers will be normalized at 3 digits, which is also the default.  Usage is dependent on proper use of letter case for Elements.

 CHNO C-001H-001N-001O-001  =personal.xls!ChemNDigits(A11)
 C3H2 C-003H-002 =personal.xls!ChemNDigits(A12)
 C6H12NO2 C-006H-012N-001O-002  =personal.xls!ChemNDigits(A13)
 C10H5F3 C-010H-005F-003  =personal.xls!ChemNDigits(A14)
 C12H6FO2 C-012H-006F-001O-002  =personal.xls!ChemNDigits(A15)
 C12H6F3 C-012H-006F-003  =personal.xls!ChemNDigits(A16)
 Cs4H20 Cs004H-020 =personal.xls!ChemNDigits(A17)

To print Cs4H20 as Cs4H20 you can use a subroutine posted by David Hagar, programming, 2002-01-22

Function ChemNDigits(cell As String, Optional p As Long) As String
  ' David McRitchie, newusers,  2004-07-01
  '  & tcpip.htm
   Dim i As Long, n As String, s As String, c As String
   Dim newstr As String
   s = Trim(cell)
   If p = 0 Then p = 3
   newstr = ""
   n = 0
   '------ end of initialization
   c = Left(s, 1)
   For i = 2 To Len(s)
     If Mid(s, i, 1) Like "[A-Z]" Then
        If c <> "" Then
          newstr = newstr & Left(c & "--", 2)
        End If
        n = Application.WorksheetFunction.Max(1, n)
        newstr = newstr & Format(n, Left("0000000", p))
        n = 0
        c = Mid(s, i, 1)
     ElseIf Mid(s, i, 1) Like "[a-z]" Then
        c = c & Mid(s, i, 1)
     ElseIf Mid(s, i, 1) Like "[0-9]" Then
        If c <> "" Then newstr = newstr & Left(c & "--", 2)
        n = n & Mid(s, i, 1)
        c = ""
        newstr = newstr & "..error.."
     End If
  Next i
  If c <> "" Then newstr = newstr & Left(c & "--", 2)
  n = Application.WorksheetFunction.Max(1, n)
  newstr = newstr & Format(n, Left("0000000", p))
  ChemNDigits = newstr  'Chemical Nomenclature
End Function

Sort on Domain Name (#domain)

Domain name within email address

Use a helper column to obtain the domain name:

Domain name within url

Some wiki notes on Host name and on Domain name.  One cannot actually tell what the server host name is just by looking at a url but the following will work for what it would appear to be in the US, some attempt has been made to make it work with two letter country codes.  I used the following to create a domain column based on the host column and does what I want it to do for me in the US.  It doesn't actually work well with state, county, and city goverments using ".us" but it works fine for companies, and learning institutions (in the US).
Function StrDomain(cell As Range) As String
 Dim newstr As String
 Dim i As Integer, j As Integer, k As Integer
 newstr = Replace(cell, "//", ".") 
 j = 1: k = 0
 For i = 2 To Len(newstr)
   If i + 2 = Len(newstr) Then GoTo done
   If Mid(newstr, i, 1) = "." Then
     k = j
     j = i + 1
   End If   
 Next i
done: StrDomain = Mid(cell, k + 1, Len(cell) - k)
End Function
You are one of many distinguished visitors who have visited my site here or in a previous location  Document created some time between begining of 1998 and end of 2004.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved