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.

7KANWTF 7COAWTF KAN 7WTNWTF 7COAWTF WTN 7NEBWTF 7COAWTF NEB 7SFLWTF 7COAWTF SFL 7RUTWTF 7COAWTF RUT 7LOUWTF 7COAWTF LOU 7MIAWTF 7COAWTF MIA 7TAMWTF 7COAWTF TAM 7NCSWTF 7COAWTF NCS 7GATWTF 7COAWTF GAT 7ASUWTF 7COAWTF ASU 7ECAWTF 7COAWTF ECA 7INDWTF 7COAWTF IND 7MSSWTF 7COAWTF MSS 7KANWTF 7COAWTF KAN 7KANWTF 7COAWTF KAN <colgroup><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[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

raw	string	result	result2
7KANWTF	7COAWTF	KAN	KAN
7WTNWTF	7COAWTF	N	WTN
7NEBWTF	7COAWTF	NEB	NEB
7SFLWTF	7COAWTF	SFL	SFL
7RUTWTF	7COAWTF	RU	RUT
7LOUWTF	7COAWTF	LOU	LOU
7MIAWTF	7COAWTF	MI	MIA
7TAMWTF	7COAWTF	M	TAM
7NCSWTF	7COAWTF	NCS	NCS
7GATWTF	7COAWTF	G	GAT
7ASUWTF	7COAWTF	SU	ASU
7ECAWTF	7COAWTF	E	ECA
7INDWTF	7COAWTF	IND	IND
7MSSWTF	7COAWTF	MSS	MSS
7KANWTF	7COAWTF	KAN	KAN
7KANWTF	7COAWTF	KAN	KAN

[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)