Find the part of the string that distinguishes a string in one cell from a string in another cell

Question

I have two columns with strings and want to know which parts of the string from one column has with the other column. I have checked this solution Compare 2 text cells and show difference in the third cell | Super User but it does not work for my problem.

In the screenshot below, I want to the result column to have the string Elementary since this is what differentiates B2 from A2. I tried using =SUBSTITUTE(A2,B2,"") based on the solution in the link above, but what the formula does is do the opposite by displaying the common strings in both columns:

How can I make it display the string difference (Elementary) instead?

Answer 1

How can I make it display the string difference (Elementary) instead?

Here's a custom function called WORDDIF that may do what you want.

To install the custom function...

• Alt+F11 to open the VBA Editor
• From the VBA menu, select Insert > Module
• Paste the code below in the VBA Edit window

Back in Excel, put this formula in C1

=WORDDIF(A1,B1)

Code:

Function WORDDIF(rngA As Range, rngB As Range) As String

    Dim WordsA As Variant, WordsB As Variant
    Dim ndxA As Long, ndxB As Long, strTemp As String

    WordsA = Split(rngA.Text, " ")
    WordsB = Split(rngB.Text, " ")

    For ndxB = LBound(WordsB) To UBound(WordsB)
        For ndxA = LBound(WordsA) To UBound(WordsA)
            If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then
                WordsA(ndxA) = vbNullString
                Exit For
            End If
        Next ndxA
    Next ndxB

    For ndxA = LBound(WordsA) To UBound(WordsA)
        If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "
    Next ndxA

    WORDDIF = Trim(strTemp)

End Function

Source https://www.mrexcel.com/forum/excel-questions/486708-compare-two-strings-find-difference.html