Compare two strings and highlight the different words

[arvindmotha]

Someone please help with VBA code to find difference between 2 strings and highlight the exact words in a separate cell along with the original text. Should work well even for relatively large text fields like 500-1000 words.

A1 = The quick brown fox jumps over the lazy dog
B1 =The quick brown fox over the lazy dog

C1 should return = The quick brown fox jumps over the lazy dog

Thanks in advance!

 

[Peter_SSs]

Can you give us the two sample texts in a form that we can copy to test? We cannot copy from an image.

 

[HenkDePoema]

Thank you sharing. This worked perfectly well for small texts as shown in your results. But larger text field matches had some anomalies. Even words with no differences were getting highlighted

View attachment 66782

Your posts are mixed up indeed. The above image shows the (poor) result with my code. Below is the result with @Peter_SSs 's code:

Also not exactly what we're after, but his approach using Regex is much more promising than mine.

Here is the original text of A1:

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

And here with missing words, in B1:

Lorem Ipsum is simply text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

 

[Peter_SSs]

This worked perfectly well for small texts as shown in your results. But larger text field matches had some anomalies.

It is not so much small and large but your original one sample had no punctuation and no digits. The latest text has both.

Updated code:

Sub DifferentWords_v2()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Range("A2", Range("B" & Rows.Count).End(xlUp))
    a = .Value
    .Font.Color = vbRed
    For i = 1 To UBound(a)
      RX.Pattern = "(^| )(" & Replace(Replace(a(i, 1), " ", "|"), ".", "\.") & ")(?= |$)"
      For Each M In RX.Execute(a(i, 2))
        .Cells(i, 2).Characters(M.FirstIndex + 1, M.Length).Font.Color = 0
      Next M
      RX.Pattern = "(^| )(" & Replace(Replace(a(i, 2), " ", "|"), ".", "\.") & ")(?= |$)"
      For Each M In RX.Execute(a(i, 1))
        .Cells(i, 1).Characters(M.FirstIndex + 1, M.Length).Font.Color = 0
      Next M
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Result based on text provided in post 12:

 

[HenkDePoema]

Great result, neat solution! I do wonder though why it doesn't find the missing "dummy" on the first line.

 

[Peter_SSs]

I do wonder though why it doesn't find the missing "dummy" on the first line.

Because dummy is found in both cells.
The code is highlighting words that are in one cell but not in the other.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Compare two strings and highlight the different words
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.