Return digit that does not exist in a cellClosed

Question

Good Day all,

Cell A2 is 012 in text format.  Cell B2 is 0821 in text format too.  
Is there a function that can check value in cell B2 against cell A2 and return the digit that does not exist in cell A2?  
For example, cell B2 is 0821, when checked agained cell A2's value of 012, it will return digit 8 which does not exist in A2.


Configuration: Windows XP / Firefox 3.6.3

rizvisa1 · Answer

what would be the maximum length of values in either cells

nickname · Answer

column A will have 3-digit numbers e.g 012, column B will have 4-digit numbers e.g 2180.  I would like column C to return the missing digit or in case there is no missing digit, to return the smallest digit that match the 4-digit numbers in column A.

rizvisa1 · Answer

try this


Function myCompare(compareThis As String, compareAgainst As String)
Dim StringAbsent As String
Dim MaxAbsent As String
Dim MinAbsent As String
Dim CheckThis As String

Dim iCounter As Long

    MaxAbsent = ""
    MinAbsent = ""
    StringAbsent = ""
    
    For ichar = 1 To Len(compareThis)
        
        CheckThis = CStr(Mid(compareThis, ichar, 1))
        present = CStr(InStr(1, CStr(compareAgainst), CheckThis))
        
        If present > 0 Then
                
            If (CheckThis > MaxAbsent) Then MaxAbsent = CheckThis
            If (CheckThis < MinAbsent) Then MinAbsent = CheckThis
            If (MinAbsent = "") Then MinAbsent = CheckThis
        Else
            
            StringAbsent = StringAbsent & CheckThis
        
        End If
        
        
    Next ichar

    If StringAbsent = "" Then
        myCompare = MinAbsent
    Else
        myCompare = StringAbsent
    End If
    
End Function

Return digit that does not exist in a cell

3 responses

Similar discussions

Subscribe To Our Newsletter!