Counting Text Pairs In a String of Text (ie "AA", "GC" etc)

**smccann920** · 03-31-2010, 06:17 PM

Hi,

I'm new to this forum so I'm not sure if this is the best place for this question. If I should post it under a different heading let me know.

I'm trying to get excel to count how many times a pair of letters appears in a string. I'm working with DNA so I'm only using A, G, C and T.

For example in a sequence GTA AAA CGA there is 1 GT pair, 1 TA pair, 3 AA pair, 1 AC pair, 1 CG pair, and 1 GA pair.

I set up columns for every possible combination and have them counted with this formula
=((LEN(C3)-LEN(SUBSTITUTE(C3,"AA","")))/2)
There was probably an easier way to do it but this worked for me.

My problem happens when there is a series of poly letters (like in the sequence above).
Any help would be appreciated! Thank you!

**martindwilson** · 03-31-2010, 08:00 PM

why is it 3 aaa and come to that where is ac or are the spaces misleading?

**smccann920** · 03-31-2010, 08:09 PM

Sorry the spaces are irrelevant, it's just how DNA is usually listed. That should be one string of text with 9 characters.

**Rebuild8** · 03-31-2010, 08:22 PM

Attached is one possible solution. It involves some helper cells (you can hide the columns if needed).

**DonkeyOte** · 04-01-2010, 03:06 AM

If your string is in A1 and your combinations are say listed in D1:D16 then you can calculate the results in E1:E16 using:

E1: =SUMPRODUCT(--(MID(SUBSTITUTE($A$1," ",""),ROW($1:$8),2)=$D1))
copied down

That would return the outlined results for sample string.