compare text in two columns and return the difference in a third

[adipric]

I am needing to compare two columns and have the third be the result what is different, keeping the characters from the first column. It cannot be based off of left or right because there are values (not in the example) where the different characters will be in different places. I need the result to look like the below example.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 652"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]7KANWTF[/TD]
[TD]7COAWTF[/TD]
[TD]KAN[/TD]
[/TR]
[TR]
[TD]7WTNWTF[/TD]
[TD]7COAWTF[/TD]
[TD]WTN[/TD]
[/TR]
[TR]
[TD]7NEBWTF[/TD]
[TD]7COAWTF[/TD]
[TD]NEB[/TD]
[/TR]
[TR]
[TD]7SFLWTF[/TD]
[TD]7COAWTF[/TD]
[TD]SFL[/TD]
[/TR]
[TR]
[TD]7RUTWTF[/TD]
[TD]7COAWTF[/TD]
[TD]RUT[/TD]
[/TR]
[TR]
[TD]7LOUWTF[/TD]
[TD]7COAWTF[/TD]
[TD]LOU[/TD]
[/TR]
[TR]
[TD]7MIAWTF[/TD]
[TD]7COAWTF[/TD]
[TD]MIA[/TD]
[/TR]
[TR]
[TD]7TAMWTF[/TD]
[TD]7COAWTF[/TD]
[TD]TAM[/TD]
[/TR]
[TR]
[TD]7NCSWTF[/TD]
[TD]7COAWTF[/TD]
[TD]NCS[/TD]
[/TR]
[TR]
[TD]7GATWTF[/TD]
[TD]7COAWTF[/TD]
[TD]GAT[/TD]
[/TR]
[TR]
[TD]7ASUWTF[/TD]
[TD]7COAWTF[/TD]
[TD]ASU[/TD]
[/TR]
[TR]
[TD]7ECAWTF[/TD]
[TD]7COAWTF[/TD]
[TD]ECA[/TD]
[/TR]
[TR]
[TD]7INDWTF[/TD]
[TD]7COAWTF[/TD]
[TD]IND[/TD]
[/TR]
[TR]
[TD]7MSSWTF[/TD]
[TD]7COAWTF[/TD]
[TD]MSS[/TD]
[/TR]
[TR]
[TD]7KANWTF[/TD]
[TD]7COAWTF[/TD]
[TD]KAN[/TD]
[/TR]
[TR]
[TD]7KANWTF[/TD]
[TD]7COAWTF[/TD]
[TD]KAN[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Snakehips]

@adipric

Is the string length constant at 7 characters?
Is it always 3 consecutive different characters?

 

[sandy666]

you said this is not representative example
so maybe with PowerQuery
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]raw[/td][td=bgcolor:#70AD47]string[/td][td=bgcolor:#70AD47]result[/td][td=bgcolor:#70AD47]result2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7KANWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]KAN[/td][td=bgcolor:#E2EFDA]KAN[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7WTNWTF[/td][td]7COAWTF[/td][td]N[/td][td]WTN[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7NEBWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]NEB[/td][td=bgcolor:#E2EFDA]NEB[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7SFLWTF[/td][td]7COAWTF[/td][td]SFL[/td][td]SFL[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7RUTWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]RU[/td][td=bgcolor:#E2EFDA]RUT[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7LOUWTF[/td][td]7COAWTF[/td][td]LOU[/td][td]LOU[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7MIAWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]MI[/td][td=bgcolor:#E2EFDA]MIA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7TAMWTF[/td][td]7COAWTF[/td][td]M[/td][td]TAM[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7NCSWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]NCS[/td][td=bgcolor:#E2EFDA]NCS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7GATWTF[/td][td]7COAWTF[/td][td]G[/td][td]GAT[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7ASUWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]SU[/td][td=bgcolor:#E2EFDA]ASU[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7ECAWTF[/td][td]7COAWTF[/td][td]E[/td][td]ECA[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7INDWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]IND[/td][td=bgcolor:#E2EFDA]IND[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7MSSWTF[/td][td]7COAWTF[/td][td]MSS[/td][td]MSS[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]7KANWTF[/td][td=bgcolor:#E2EFDA]7COAWTF[/td][td=bgcolor:#E2EFDA]KAN[/td][td=bgcolor:#E2EFDA]KAN[/td][/tr]

[tr=bgcolor:#FFFFFF][td]7KANWTF[/td][td]7COAWTF[/td][td]KAN[/td][td]KAN[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    C2R = Table.AddColumn(Source, "result", each Text.Trim([raw],{"7","C","O","A","W","T","F"})),
    TBD = Table.AddColumn(C2R, "Text Between Delimiters", each Text.BetweenDelimiters([raw], "7", "WTF"), type text),
    Rename = Table.RenameColumns(TBD,{{"Text Between Delimiters", "result2"}})
in
    Rename[/SIZE]

1st result: all characters from string column are removed from raw column
2nd result I did with a pattern: characters to remove: 1st and 3 last (in this case column string is not necessary)

 

[XOR LX]

Hi,

What version of Excel is this for?

Also, why is MIA the result for:

7MIAWTF 7COAWTF

?

Only the letters in positions 2 and 3 differ, not that in position 4.

Similarly for your result of ECA for:

7ECAWTF 7COAWTF


Regards

 

[Snakehips]

This might suit if the different characters are always consecutive.

Excel 2010
	A	B	C
2	7KANWTF	7COAWTF	KAN
3	7WTNWTF	7COAWTF	WTN
4	7NEBWTF	7COAWTF	NEB
5	7SFLWTF	7COAWTF	SFL
6	7RUTWTF	7COAWTF	RUT
7	7LOUWTF	7COAWTF	LO
8	7MIAWTF	7COAWTF	MI
9	7TAMWTF	7COAWTF	TAM
10	7NCSWTF	7COAWTF	NCS
11	7GATWTF	7COAWTF	GAT
12	7ASUWTF	7COAWTF	ASU
13	7ECAWTF	7COAWTF	EC
14	7INDWTF	7COAWTF	IND
15	7MSSWTF	7COAWTF	MSS
16	7KANWTF	7COAWTF	KAN
17	7KANWTF	7COAWTF	KAN
18	7XXAWTF	7XMAWTF	X
Sheet14

Cell Formulas
Range	Formula
C2	=IFERROR(MID(A2,MATCH(FALSE,MID(A2,{1,2,3,4,5,6,7},1)=MID(B2,{1,2,3,4,5,6,7},1),0),SUMPRODUCT((MID(A2,{1,2,3,4,5,6,7},1)<>MID(B2,{1,2,3,4,5,6,7},1))*1)),"")

I not consecutive then a simple user-defined function should sort it.

Function Diffs(Str1 As Range, Str2 As Range) As String
For i = 1 To 7
If Mid(Str1, i, 1) <> Mid(Str2, i, 1) Then Diffs = Diffs & Mid(Str1, i, 1)
Next i
End Function

Excel 2010
	A	B	C
2	7KANWTF	7COAWTF	KAN
3	7WTNWTF	7COAWTF	WTN
4	7NEBWTF	7N*B*T*	EWF
Sheet14

Cell Formulas
Range	Formula
C2	=Diffs(A2,B2)