to count an alphabet in a cell

[Rajesh Kamath]

Hi,

Can someone help me,

my issue is, if for example there is text entered in one cell like
"aabcd" .I want to count the number time "a" appearing in the cell.

Thanks

Rajesh Kamath

 

[Paul B]

Rajesh, here is one way, =SUM(LEN(A1))-SUM(LEN(SUBSTITUTE(A1,"a","")))
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[Bernard Liengme]

With a string of 8 or les characters, this works:
=SUM(--(MID(A1,{1,2,3,4,5,6,7,8},1)="a"))
Enter it as array formula using SHIFT+CTRL+ENTER
best wishes

 

[Rajesh Kamath]

Paul,

It works fine, Thanks a ton.

 

[RagDyer]

Really no need for the SUM() function:

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

Also, to make it easier to change, you could assign a cell to enter the
character(s) that you wish to count, say D1:

=LEN(A1)-LEN(SUBSTITUTE(A1,$D$1,""))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Rajesh, here is one way, =SUM(LEN(A1))-SUM(LEN(SUBSTITUTE(A1,"a","")))
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **