You are on the Home/Excel/Excel Tips/Delimited Numbers page
Google
Web This Site

Find the minimum of delimited numbers

There are many instances when one wants to compare numbers that are separated by delimiters.  Examples of such numbers are IP addresses, which look like 192.168.0.9, and 192.168.0.10 or book section identifiers, which look like 2.1, 2.1.2, and 3.2.4, and maybe even Dewey Decimal Classification numbers  While a person would sort 192.168.0.9 before 192.168.0.10 quite naturally, it required the person to decompose each compound number into its elements (192, 168, 0, 9 and 192, 168, 0, 10, respectively) and do a mental comparison going element by element until one number was different from another.  Unfortunately, this exercise, incredibly simple for a human brain, is anything but for a computer.

Most computer languages would treat each of the compound numbers above as a "string" literal and use string comparison rules when comparing the strings.  Consequently, 192.168.0.9 would be considered as being greater than 192.168.0.10.  You can see for yourself in Excel or in VBA.

The code below lets one compare delimited integers (like the examples above).  Put the code in a standard module of an Excel workbook.  One can then use the function DelimIntMin either as a function called by other code (as in the test routine testDelimIntMin) or in an Excel worksheet as a User Defined Function (also called a UDF).  When used as a function in an Excel worksheet, each argument must be a string or a single cell.  For example, if J2 contains 192.168.0.9, and K2 contains 192.168.0.10, the minimum can be found with =DelimIntMin(J2,K2) but not =DelimIntMin(J2:K2).  Hopefully, at some point in the future I will enhance the code to accept range arguments.  Future plans also include writing the function DelimIntMax, which will return the maximum value for a set of delimited integers.

The function DelimIntMin allows for an optional delimiter as the last argument.  The way the function figures out one is present is by checking if the last argument has a length of 1.  If so, it assumes that the argument is a delimiter.  If none is provided, the default is the period character.

Option Explicit
Option Base 0
Private Function Min(ParamArray X() As Variant)
    Min = Application.WorksheetFunction.Min(X)
    End Function
Private Function ArrMin(Arr1, Arr2) As Variant
    Dim I As Integer
    For I = 0 To Min(UBound(Arr1), UBound(Arr2))
        If Arr1(I) < Arr2(I) Then
            ArrMin = Arr1: Exit Function
        ElseIf Arr1(I) > Arr2(I) Then
            ArrMin = Arr2: Exit Function
            End If
        Next I
    'arr1 and arr2 are equal to the number of common elements
    ArrMin = IIf(UBound(Arr1) < UBound(Arr2), Arr1, Arr2)
    End Function
Private Function convertToInteger(ByVal Arr)
    Dim I As Integer, Rslt
    ReDim Rslt(UBound(Arr))
    For I = LBound(Arr) To UBound(Arr)
        Rslt(I) = CInt(Arr(I))
        Next I
    convertToInteger = Rslt
    End Function
Public Function DelimIntMin(ParamArray DelimNbr() As Variant)
    Dim Separator As String, SeparatorPresent As Boolean
    If Len(DelimNbr(UBound(DelimNbr))) = 1 Then
        Separator = DelimNbr(UBound(DelimNbr))
        SeparatorPresent = True
    Else
        Separator = "."
        End If
    Dim Arr, I As Integer
    ReDim Arr(UBound(DelimNbr) - IIf(SeparatorPresent, 1, 0))
    For I = LBound(Arr) To UBound(Arr)
        Arr(I) = convertToInteger(Split(DelimNbr(I), Separator))
        Next I
    Dim Rslt
    Rslt = Arr(LBound(Arr))
    For I = LBound(Arr) + 1 To UBound(Arr)
        Rslt = ArrMin(Rslt, Arr(I))
        Next I
    DelimIntMin = Join(Rslt, Separator)
    End Function
Private Sub testDelimIntMin()
    MsgBox DelimIntMin("3.3.9", "3.3.10", ".")
    MsgBox DelimIntMin("1.2", "1.1.9")
    MsgBox DelimIntMin("4.1", "3.3.10")
    MsgBox DelimIntMin("1.2", "1.2.9")
    MsgBox DelimIntMin("4.1", "3.3.10", "3.3.9", "3.1", ".")
    MsgBox DelimIntMin("4,1", "3,3,10", "3,3,9", "1,1,1,1,1", ",")
    End Sub