Excel Mac 2019 Compare Characters In 2 Cells Returning Difference To A Third Cell

[PaulW75]

0
I have an Excel sheet with two columns (A & B) each cell containing text, I would like to be able create a third column (C) with the characters that are different between the two.
There are 50,000 rows to go through.

An Example:

I have attached an image as an example

Another Example:

Column A2: ABCDEFG
Column B2: AB"CDEFGED
Column C2: "ED (The difference between the 2)

Is this possible? Thanks for any help in advance. Working on Excel Mac 2019

 

[JoeMo]

Why does the difference in C3 not include the apostrophe (') from A3, and why are there 2 instances of "I" in C3?

 

[PaulW75]

Sorry apostrophe (') and the 2 instances are my fault making a mistake in the example, apologies

 

[Larry Haydn]

First, think about the rules:
* Does the position of the characters have to match?
* If not, can the matching characters be limited from both data one by one, until one of the data is zero length?

 

[PaulW75]

Hi Larry, thank you for your reply. In answer to the questions:

i) No
ii) Yes I believe that would work, though no idea how to do.

 

[Larry Haydn]

Let me write out some code later. Basically comparing the two strings, and eliminate the common letters one by one.

 

[PaulW75]

Thank you very much

 

[JoeMo]

Here's a UDF (User-defined Function) you can try after you install it. See the example for how to use it.
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Book1
	A	B	C
1	Name 1	Name 2	Difference
2	AB CDE	AB CDE"FG	"FG
3	L'ABCDE"TD	ABCDELIMITED	'"IM
4	Paul123	Paul123
5	*12 ABCDE	12 ABCDE	*
Sheet8

Cell Formulas
Range	Formula
C2:C5	C2	=Diff(A2,B2)

Function Diff(S1 As String, S2 As String) As String
Dim i As Long, j As Long, d1 As Object, d2 As Object
Set d1 = CreateObject("Scripting.dictionary")
Set d2 = CreateObject("Scripting.dictionary")
For i = 1 To Len(S1)
    If Not d1.exists(Mid(S1, i, 1)) Then d1.Add Mid(S1, i, 1), d1.Count
Next i
For i = 1 To Len(S2)
    If Not d2.exists(Mid(S2, i, 1)) Then d2.Add Mid(S2, i, 1), d2.Count
Next i
For i = 0 To d1.Count - 1
    If Not d2.exists(d1.keys()(i)) Then Diff = Diff & d1.keys()(i)
Next i
For i = 0 To d2.Count - 1
    If Not d1.exists(d2.keys()(i)) Then Diff = Diff & d2.keys()(i)
Next i
End Function