Counting occurences of a specific letter in a string

SDM · May 30, 2003

I use Excel 2000 as a database for DNA sequences. The sequences are each entered as a string of letters (ex: ACGTCGCTGCGTACGT). I have been trying to find a way to count the occurences of each letter in the string so I can have Excel automatically calculate molecular weights (# of A's, # of C's, etc.).
Do any of you have any suggestions?
Thanks!
-Scott

Juan Pablo González · May 30, 2003

Hi, try this

=LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")

where A1 is your main string, and B1 is the letter that you want to count.

SDM · Jun 3, 2003

Thank you!
For anyone who is interested, here is the result:

=IF(A1="","",((LEN(A1)-LEN(SUBSTITUTE(A1,"A","") ))*313.2114)+((LEN(A1)-LEN(SUBSTITUTE(A1,"C","") ))*289.1863)+((LEN(A1)-LEN(SUBSTITUTE(A1,"G","") ))*329.2108)+((LEN(A1)-LEN(SUBSTITUTE(A1,"T","") ))*304.1981)-61.9646)

where A1 contains the sequence (ex: ACGTCGATCGAC)

-Scott

rrdonutz · Jun 3, 2003

Scott, try the following formula:

=IF(A1="","",SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{"A","C","G","T"},"")))*{313.2114,289.1863,329.2108,304.1981})-61.9646)

You get the same results, and the formula's a little easier on the eyes.

--Tom

Counting occurences of a specific letter in a string

SDM

New Member

Excel Facts

Juan Pablo González

MrExcel MVP

SDM

New Member

rrdonutz

Well-known Member

Similar threads

Forum statistics

Share this page

Counting occurences of a specific letter in a string

SDM

New Member

Excel Facts

Juan Pablo González

MrExcel MVP

SDM

New Member

rrdonutz

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock