Sorting TCP/IP Addresses, and the like

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
	192.168.119.6	192.168.119.006
	192.168.22.6	192.168.022.006
	192.168.220.6	192.168.220.006

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.

	E	F	G
20	1.1.1.1	001.001.001.001	=personal.xls!IPSort(E20)
21	1.1.1.2	1.1.1.2	=personal.xls!IPNorm(E21)
22	001.001.001.001	1.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
    http://www.mvps.org/dmcritchie/excel/formula.htm
which includes information on installing Functions and Subroutines.

TCP number represented in Decimal (#tcpn)

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)

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 = ""
reloop:
   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
   Else
     If Len(oldstr) - i >= 4 Then
        ChaptSort = "#length"
        Exit Function
     Else
       newstr = newstr & "." & Left("0000",  _
          3 - (Len(oldstr) - i)) & Mid(oldstr, i)
     End If
   End If
   ChaptSort = "*" & Mid(newstr, 2)
End Function

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

1.1.1.1	001.001.001.001	=personal.xls!NormDigits(A1)
31.32.34.250	031.032.034.250	=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 = ""
reloop:
   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)
     Else
       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 Cs₄H₂₀ 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
  ' http://www.mvps.org/dmcritchie/sorting.htm  & tcpip.htm
  ' http://google.com/groups?threadm=uNAGaA5XEHA.2840@TK2MSFTNGP11.phx.gbl
   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 = ""
     Else
        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:
  =IF(ISERR(FIND("@",A1)),"",RIGHT(A1,LEN(A1)-FIND("@",A1,1)))

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

Related Information (#related)

• Notes on Sorting This is my primary page on Sorting.
• Get the IP Address (dead link), gives the code to get the user's IP address through the API: (VBA), Sam Huggill
• How to Ping an IP Address Using Visual Basic, (VBA) Randy Birch
• What is My IP Address, web page get IP address, and domain name (with advertisment)
• Shields Up, Steve Gibson's site to test your security.
• Basic TCP/IP Information (archived copy) from the Tech Section of http://www.ezine.com (also available as Word document).
• ExtractDigits, User Defined Function, extract first set of consecutive digits from a string.  Treat as a number if found as an empty string if none found (i.e., g25abc16 would return 25).  In the next posting in same thread is a means to extract set of digits from left/middle/right using an array formula, but it fails if you have two sets of digits in the same string (i.e. g25abc16 would fail).
• Sorting IP Address in VB (dead link), converts TCP/IP address to or from a 32 bit LONG integer.

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