excel formula help (identifying letters)

say in a1 i have "cat"

what can i put in a2 to tell me the number of cs in a1? for example, the answer is 1.

thx

 

the number of 'cs'? please clarify.

 

the number of times "c" is in the cell. for example, there is only 1 "c" in cat, so the answer is 1.

another example..... if i had "beer" in a1 and i wanted to count the number of es. the answer would be 2.

 

I dont think you can use a formula...
Create a vba for this.

OHLC

 

Sub Myassitches()

Range("A1").select

Temp = activecell.value

count = 0

for i = 1 to len(Temp)
if mid(Temp,1,"c") then count = count +1
Next i


msgbox Count

End sub

 

I think you can use a formula using the same parameters, LEFT, MID, RIGHT, LEN, etc... if you don't want to use VBA. If you don't figure it out by later tonight, I'll write you a formula.

 

FishSauce almost got it...

Sub TheresAnOintmentForThat()

Temp = Range("A1").Value
Letter = Range("B1").Value

Count = 0

For i = 1 To Len(Temp)
If Mid(Temp, i, 1) = Letter Then Count = Count + 1
Next i


MsgBox Count

End Sub

' Put your word in A1 and the letter you want counted in B1.

 

thx, dudes... i'll get it to work somehow. if not, i'll be back.

 

Gordon,

Put this in any cell (other than A1 !!) and it will return the number of C's or c's (lower or upper case) you have in A1.

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"C",""),"c",""))

Just alter the C and c to other stuff if you want to check for other events .



stu

 

Very nice use of the SUBSTITUTE function. I had not thought of that one. Great suggestion.