Finding/counting a given character within a cell

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

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
>

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" <petersod@verizonXSPAM.net> wrote in message
news:4485AFFB.279A3B55@verizonXSPAM.net...

> 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" <petersod@verizonXSPAM.net> wrote in message
> news:4485AFFB.279A3B55@verizonXSPAM.net...
>
> > 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" <petersod@verizonXSPAM.net> wrote in message
> news:4485AFFB.279A3B55@verizonXSPAM.net...
>
> > 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