excel formula help (identifying letters)

Quote from Gordon Gekko:

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
More...
the number of 'cs'? please clarify.
 
Quote from richtrader:


the number of 'cs'? please clarify.
More...
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.
 
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.
 
Quote from Gordon Gekko: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
More...

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
 
You must log in or register to reply here.
Back
Top