Compare character strings in 2 cells and return only the differences

Hi all,

Was hoping someone could provide some help with a problem I've been working on. I have a data set that contains very similar data save for one or 2 characters. I'm trying to isolate those characters using a formula but can't seem to figure out a proper way to do that.

For example

Example 1

Cell 1 - CARA123
Cell 2 - CARA123#

(in this example, i'd like the cell to indicate # as the unmatched character)

Example 2

Cell 1 - ARNA<
Cell 2 - ARNA

(in this example, i'd like the cell to indicate < as the unmatched character)


Another possibility that may solve my dilemma is a formuala that will compare each character in the cell one by one and only bring back the results starting where the differences occur.

For example

Cell 1 - ARNAFED123
Cell 2 - ARNABED123

The formula would then bring back BED123 as the answer (as the B was the point where the 2 cells "became unmatched").

Any help provided would be greatly appreciated.

Well, I don't want to say that one cell is right and one cell is wrong but for the sake of this example, let's say that the cell with the "character" is wrong.

Originally Posted by mcmahobt

What I mean is, for text that don't match character length, it is easy to single out the extra characters. However, for text lengths that match, such as your last example, why would it be BED123 that is the answer, as opposed to FED123?

Yeah I think that 2nd example was very unclear. I guess I'm going on the assumption that cell 1 is correct....but it was a completely different scenario.

Sorry for the late response everyone. Thank you so much for all the help!

I was able to produce the results i needed using the first formula. I'm still messing around with all of them though to see how I could apply them to the existing data set (or future data sets).

You've all been an amazing help!