Finding/counting a given character within a cell

"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