count the number of alphabets in a given cell

[priyanka]

Hello everyone,
Can anybody help me out- how to count the number of alphabets in a particular cell in excel.I am dealing with nucleotides. So, all cells have only alphabets A,C,G,T.
For eg: if the cell has agtcttgcaaatgcctaaaa
i want to know number of a's, t's, c's, d's in that particular cell...

Thank u so much in advance
Priyanka

 

[jeffreybrown]

Hi Priyanka,

Would this work...

Excel Workbook
	F	G
2	agtcttgcaaatgcctaaaa	20
3
4	a	8
5	c	4
6	g	3
7	t	5
8		20
List

 

[Colin Legg]

Hello,

Genetics work, by any chance?

Say that string was in the cell A1, to count the number of "a"'s, you could use this:

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

 

[Kaps_mr2]

The easiest way is to create a User defined function. At the bottom of your sheet right click and highlight "View code".

Then at the top you will see a menu bar - click on Insert and then "Insert module".

and then copy and paste the code below.

then click File, close to Excel.

then if you want to count the number of "a"'s in aaag you type:-

=Count_Letters("aaaag","a")

Thanks

Kaps

Public Function Count_Letters(Text As String, Letter As String) As Long
Dim c As Long
Count_Letters = 0
 
For c = 1 To Len(Text)
    If Ucase(Mid(Text, c, 1)) = Ucase(Letter) Then
        Count_Letters = Count_Letters + 1
 
    End If
 
Next c
End Function

 

[priyanka]

Thank u so much.. All are working very well.. Thank all for an immediate response..