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.

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" wrote:

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,
Your user-defined function did the trick! Thanks a million!
Regards, Bob

"hans bal(nl)" wrote:

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" wrote:

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.

=(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")



Bob wrote:

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.

--

Dave Peterson

Try this

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


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=549007

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

"Dave Peterson" wrote:

=(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")



Bob wrote:

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.

--

Dave Peterson

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


"Bearacade" wrote:


Try this

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


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=549007

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



Bob wrote:

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

"Dave Peterson" wrote:

=(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")



Bob wrote:

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.

--

Dave Peterson


--

Dave Peterson

"Dave Peterson" wrote in message
...

Bob wrote:

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.

=(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?
--
David Biddulph

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

--
Kevin Vaughn


"David Biddulph" wrote:

"Dave Peterson" wrote in message
...

Bob wrote:

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.

=(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?
--
David Biddulph

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.



David Biddulph wrote:

"Dave Peterson" wrote in message
...

Bob wrote:

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.

=(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?
--
David Biddulph

--

Dave Peterson