Excel= can I count how many times letter a appears in 1 cell?

[Guest]

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?

 

[David Biddulph]

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

 

[Peo Sjoblom]

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

 

[Gord Dibben]

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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

 

[Ragdyer]

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"),""))

 

[Peo Sjoblom]

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


--

Regards,

Peo Sjoblom

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

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


--

Regards,

Peo Sjoblom