Finding/counting a given character within a cell

[Bob]

For a given cell, I need to count the number of times a given character
(e.g., the letter "a") appears. Is there a worksheet function or formula
that will help me do this? Thanks for any help.

 

[hans bal(nl)]

You can use this user-defined function:

Function CountChar(MyChar, Mystring)
Dim counter As Integer

CountChar = 0

For counter = 1 To Len(Mystring)
If Mid(Mystring, counter, 1) = MyChar Then CountChar = CountChar + 1
Next counter

End Function


Go to tools-macro-visual basic editor and enter the text above.

You can then use this function like this :

=countchar("a",B1)

( assuming the cell you want to evaluate is B1)


HTH

 

[Bob]

Hans,
Your user-defined function did the trick! Thanks a million!
Regards, Bob

 

[Dave Peterson]

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a")
Will count the number of A's or a's in A1.

If you want just the lower case a's:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a")

 

[Bearacade]

Try this

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

 

[Bob]

Dave,
Thanks for your solution and your help! I am always amazed at the power of
the SUBSTITUTE function.
Regards, Bob

 

[Bob]

Bearacade,
Thanks for your help! I sincerely appreciate it.
Regards, Bob

 

[Dave Peterson]

I should have included that =substitute() is case sensitive. That's why I
included two versions.

 

[David Biddulph]

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a")
Will count the number of A's or a's in A1.

If you want just the lower case a's:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a")

What is the division by LEN("a") doing? Isn't LEN("a") equal to 1?

 

[Kevin Vaughn]

I think you're right. It looks like the /1 is not needed.

 

[Dave Peterson]

Yep.

But anyone who uses google may want to find the number of times "David" appears
in a cell.

Then the formula is easily changed--it's kind of self documenting.