Subtracting duplicated text between cells of two columns

Hallo all,

Have been driving myself crazy trying to figure this out and would really appreciate any help!!

My goal is basically the following :

A1 : DOG,CAT,BIRD
B1: DOG,CAT,PIG,BIRD and I want to identify the additional word in a results column i.e.
C1: PIG

Is there a way to do this? The text content keeps changing so i cannot do a find and replace or specifically output a given value.

Thanks in advance!!

Thanks for the interest.

Yes - there will always be precisely one additional word in a single cell with an exact length of 3 characters. And each word will always be comma-seperated.

oh oops, and the extra word will always be in column B - never in A.

Sorry I was mistaken. The extra words could be in either column A or B. But figuring out either way would be helpful.

Originally Posted by sp86

oh oops, and the extra word will always be in column B - never in A.

Thanks so much XOR. I keep getting an error when i try using this with respect to the LEN. Will give it a bit more of a go and try to figure it out as you were nice enough to come up with it. With arrays is it just that you have to complete with CTRL+SHIFT+ENTER ?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,LEFT(A1,FIND(",",A1)-1),""),MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1),""),MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,256),""),",","")
try this

if it not works upload sample file

@XOR : Thanks again - perhaps i am just entering it wrong as an array and thats why i cant get it t work I would be totally fine with having it in two different cells and just changing A and B in the formula if i could get it to work at all!

@nfl : Thanks as well, but unfortunately its true, whenever there are more than a few words i dont get a clear output.

Have attached a sample of my data here. In the meantime i shall also try to understand entering arrays better!

Originally Posted by XOR LX

Correction to my previous formula (again, must be array-entered):

=INDEX(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(B1)-LEN(SUBSTITUTE(B1,",",""))))-1)+1,255)),MATCH(0,MMULT(TRANSPOSE(IF(ISERR(SEARCH(TRANSPOSE(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(B1)-LEN(SUBSTITUTE(B1,",",""))))-1)+1,255))),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1)+1,255)))),0,1)),(ROW(INDIRECT("1:"&1+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))^0)),0))

I suggest that you make a copy of this formula in a further column and replace all instances of A1 with B1 and vice versa. Alternatively, if you absolutely insist on using a single cell, I can adapt the above to detect whichever of A1 and B1 contains the surplus word, though it will most likely mean lengthening the formula considerably.

@nflsales: what if there are e.g. five words in A1 and six in B1?

Regards

XOR - thank you so much. This works, and actually in the end i realised I got more info out of having them in 2 columns seperately, and I was able to do a quality check thanks to you noticing that sometimes more than one did actually change which shouldnt happen!

Super thanks again - and I hope i can one day return the favor!