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?

 

[ElaineTay]

Hi AlphaFrog,

Thanks for the fast reply! Ok I have started a new thread.

 

[Thierry311]

Hi AlphaFrog,
I tried everything I found in this thread but no success. Here is what I am trying to achieve:
A1: TU31, TU42, TU56, TU183, TU272
A2: TU31, TU42, TU56, TU68, TU183, TU272, TU366, TU367
Result in A3: TU68, TU366, TU367
Thank you for your help.

 

[AlphaFrog]

Hi AlphaFrog,
I tried everything I found in this thread but no success. Here is what I am trying to achieve:
A1: TU31, TU42, TU56, TU183, TU272
A2: TU31, TU42, TU56, TU68, TU183, TU272, TU366, TU367
Result in A3: TU68, TU366, TU367
Thank you for your help.

Just modified the delimiter for the Worddif function in post #2

Formula:
=WORDDIF(A2,A1)

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 = Left(strTemp, Len(strTemp) - 2)
   
End Function

 

[Rick Rothstein]

Here is another UDF (user defined function) that you can try...

[table="width: 500"]
[tr]
	[td]Function Differences(ByVal Str1 As String, ByVal Str2 As String) As String
  Dim Temp As String, Arr1 As Variant, Arr2 As Variant, Var As Variant
  Arr1 = Split(Str1, ",")
  Arr2 = Split(Str2, ",")
  If UBound(Arr2) > UBound(Arr1) Then
    Str2 = Replace(Str2, ",", "")
    For Each Var In Arr1
      Str2 = Replace(Str2, Var, "", , , vbTextCompare)
    Next
    Differences = Replace(Application.Trim(Str2), " ", ", ")
  Else
    Str1 = Replace(Str1, ",", "")
    For Each Var In Arr1
      Str1 = Replace(Str1, Var, "", , , vbTextCompare)
    Next
    Differences = Replace(Application.Trim(Str1), " ", ", ")
  End If
End Function[/td]
[/tr]
[/table]

 

[AlphaFrog]

It's essentially the same code. What's the point? Is it your goal to hijack every one of the threads I reply to.

 

[Rick Rothstein]

It's essentially the same code. What's the point?

I am not sure why you are saying that... I don't consider the code you posted in Message #63 to be at all "essentially the same code" as what I posted... as a matter of fact, I consider them to be completely different. Your code runs three individual loops, one of which is embedded within another which, in effect, multiplies the number of iterations for those two loops (although you do have a short-circuit exit routine that helps limit the multiplier effect when and if one item is found within the other) every time your function is called whereas my code runs only single loop per call (and that loop iterates the shorter list only). Your code appears to be sensitive to the order the ranges are fed into it... it seems to generate a #VALUE ! error if the rngA argument contains less items in its list than the rngB argument list. Your code is working solely with arrays whereas my code is a hybrid working with one array (the shorter list) and the original text string from the longer list. Our routines do not, at their heart, for the most part, even use the same functions. So, again, I am not sure why you posted what you did.

Is it your goal to hijack every one of the threads I reply to.

I am sorry you feel that way because that is surely not my intent. When I think I have a solution that differs from any that have been provided to that point in time within a thread, I post it, as I did here, because I think it could be useful to the OP as he/she considers how to solve the problem they came to the forum for as well possibly being useful to future readers of the thread.

 

[Thierry311]

Thank you very much AlphaFrog for the super fast answer, it works perfect !
Thank you Rick Rothstein too, it works as well.

 

[Thierry311]

I have 1 variant in some lines with AE12345 instead of TU123.
A1:TU15, TU25, TU31, TU39, TU43, TU45
A2: TU15, TU25, TU31, TU39, TU43, TU32, TU45, TU56, TU40, TU62, TU64, TU76, TU83, TU82, TU92, TU105, AE24535, TU148, TU149, TU158, TU160, TU133, TU171, TU172, TU178, TU184

In A3 AlphaFrog UDF returns #N/A first than the right result after I double click and enter A3.

Rick Rothstein UDF returns 8 instead of TU158, everything else is good.
TU32, TU56, TU40, TU62, TU64, TU76, TU83, TU82, TU92, TU105, AE24535, TU148, TU149, 8, TU160, TU133, TU171, TU172, TU178, TU184

Thank you.

 

[Rick Rothstein]

Rick Rothstein UDF returns 8 instead of TU158, everything else is good.
TU32, TU56, TU40, TU62, TU64, TU76, TU83, TU82, TU92, TU105, AE24535, TU148, TU149, 8, TU160, TU133, TU171, TU172, TU178, TU184

Here is my corrected code... it should work correctly now.

Function Differences(ByVal Str1 As String, ByVal Str2 As String) As String
  Dim Temp As String, Arr1 As Variant, Arr2 As Variant, Var As Variant
  Arr1 = Split(Application.Trim(Replace(Str1, ",", " ")))
  Arr2 = Split(Application.Trim(Replace(Str2, ",", " ")))
  If UBound(Arr2) > UBound(Arr1) Then
    Str2 = " " & Application.Trim(Replace(Str2, ",", " ")) & " "
    For Each Var In Arr1
      Str2 = Replace(Str2, " " & Var & " ", "  ", , , vbTextCompare)
    Next
    Differences = Replace(Application.Trim(Str2), " ", ", ")
  Else
    Str1 = " " & Application.Trim(Replace(Str1, ",", " ")) & " "
    For Each Var In Arr2
      Str1 = Replace(Str1, " " & Var & " ", "  ", , , vbTextCompare)
    Next
    Differences = Replace(Application.Trim(Str1), " ", ", ")
  End If
End Function

 

[Thierry311]

Here is my corrected code... it should work correctly now.

It does ! Thank you very much.