Calculate differences between text cells?

[poobear]

Hi everyone!

I have several cells with characters (amino acid sequences), but let's simplify to this:

ABCDEF
ABADEF
ABCDEG
ABCDEF

I would like to compare all cells to the first cell and get the difference sort of like this:

ABCDEF: -
ABADEF: 1


ABCDEG: 1
ABCDEF: 0

Then I will calculate these changes to percentage, but that I know..

But how can I get the amount of one-letter substitutions for each cell?

Thanks!

[Colin Legg]

Hi,

If they're always 6 characters long then the usual, native formula approach can be simplified to this:

=SUM(--(MID(A3,{1,2,3,4,5,6},1)=MID(A2,{1,2,3,4,5,6},1)))

[poobear]

Wow that worked perfect, thank you so much!

Meanwhile I created my own solution, but let me tell you how less elegant it is. The cells are not 6 characters, but always 28. In 28 columns I extract the individual characters with the MID command. In 28 more columns I use he EXACT command to get a TRUE or FALSE value depending on if the character is the same or not. In another column I calculate how many TRUE values I have for each row. That's 57 columns instead of your 1!