in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?

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

"Jack D" <Jack wrote in message
...
in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?

"Jack D" <Jack wrote in message
...
in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?

Lower or upper or both? If case sensitive use

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

if not use

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


--


Regards,


Peo Sjoblom

You could employ a UDF.

Function CountChar(InRange As Range, Letter As String) As Long
Dim rng As Range
For Each rng In InRange
CountChar = CountChar + Len(rng.text) - _
Len(Application.WorksheetFunction.Substitute(UCase (rng.text) _
, UCase(Letter), ""))
Next rng
End Function

=countchar(A1,"a") for one cell

=countchar(A1:A10,"a") for a range of cells.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

And Ron de Bruin's site for where to place codes.

http://www.rondebruin.nl/code.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP



On Wed, 31 Oct 2007 14:28:03 -0700, Jack D <Jack
wrote:

in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?

It appears that the gentlemen who suggested using formulas, allowed the case
sensitivity of Substitute to slip their minds.<bg.

This will include the capital "A" at the beginning of the sentence in the
count:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack D" <Jack wrote in message
...
in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?

Really? You might want to look at my solution again RD
<LOWER("G")


--

Regards,

Peo Sjoblom




"Ragdyer" wrote in message
...
It appears that the gentlemen who suggested using formulas, allowed the
case
sensitivity of Substitute to slip their minds.<bg.

This will include the capital "A" at the beginning of the sentence in the
count:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack D" <Jack wrote in message
...
in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?

Damn ... I must start reading entire posts! ! !

Just looked at the first formula and didn't even read your text, and simply
overlooked the second formula.

I'm very sorry Peo!

Don't have as much time to play around in here like I used to.
--

Regards,

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

"Peo Sjoblom" wrote in message
...
Really? You might want to look at my solution again RD
<LOWER("G")


--

Regards,

Peo Sjoblom




"Ragdyer" wrote in message
...
It appears that the gentlemen who suggested using formulas, allowed the
case
sensitivity of Substitute to slip their minds.<bg.

This will include the capital "A" at the beginning of the sentence in the
count:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack D" <Jack wrote in message
...
in cell a1 is the following sentence - A cat ran up a tree. Is there a
formula to find how many times the letter "a" is in cell A1?