Compare two strings, and find difference?

[EtherBoo]

This may not be possible with excel, but this is what I'm trying to do

A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?

I need C1 to return:
how your

Is this possible?

 

[AlphaFrog]

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)

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

 

[hiker95]

AlphaFrog,

Nice!

 

[EtherBoo]

WOW. Thank you 100 times over!

Now, I'm not sure if this is possible, but is it possible to do something where it will do something like this

A1: Jim-Steve-Paul-Phil
B1: Steve-Phil
C1: Jim--Phil

I'm going to make some edits to the original, concat function that made this, so I might not need the change, but it would still be a big help if possible!

Thank you so much again!

Cancel that request, I changed the concat function used to make these giant strings to have spaces, and now it's giving me exactly what I need.

Thank you SO much again!

 

[Niv]

Hello folks

I'm sorry for digging this old post out but, I need some help with that function in post 2. Original function works like a charm but can some helpful soul help me to modify it to do something like this

Cell 1: 1 2 3 4 5 6 7 8
Cell 2: 1 2 3 5 6 7 8
Outcome: - - - 4 - - - -

Replace same word with - and still show words that are different. All strings are 8 words long if it's relevant.

Thanks in advance
Niv

 

[AlphaFrog]

Replace this one line...
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "

With this...
strTemp = strTemp & IIf(WordsA(ndxA) <> vbNullString, WordsA(ndxA), "-") & " "

 

[Niv]

Works perfect, you are an absolute star AlphaFrog. Thank You very much.

 

[TwoCowz]

AlphaFrog,

Sorry for resurrecting the old post!

First off, thank you for the amazing formula, its great to see coding in VBA at its best.

I have two strings which I compare nutritional values and they are all text lots of numbers and letters ,when I implemented your function, I receive a blank. What I am assuming is, somebody entered an extra space somewhere.

Is there a way to insert some code to identify which character # (slot) the space firstly "changes" per say ? I don't think this would be a hard addition you would just return the number you identify with the "change" as numerical value, I think there's a function for it !

Cheers !

 

[AlphaFrog]

Is there a way to insert some code to identify which character # (slot) the space firstly "changes" per say ? I don't think this would be a hard addition you would just return the number you identify with the "change" as numerical value

I don't really follow what you're asking. Could you give some data examples the expected result like in the original question?

 

[TwoCowz]

When I have the two cells comparing

john jacob juinor
john jacob salt

I enter the A1 content as 1st string of words above, and the second one, the result is "junior" so it identified which value changed !

I want excel to return the character that is the letter "J" in the word junior. 11 to be exact purely for identification purposes as if I have a paragraph of ingredients I can spot where the recipe calls for sugar (packet) , sugar (cane).