General Topics
Markets
Technical Topics
Brokerage Firms
Company Specific
Community Lounge
Site Support

# excel formula help (identifying letters)

Discussion in 'Trading Software' started by Gordon Gekko, Jul 2, 2003.

1. ### 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

the number of 'cs'? please clarify.

3. ### Gordon Gekko

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.

4. ### OHLC

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

OHLC

5. ### FishSauce

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

6. ### ktm

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.

7. ### WarEagleModerator

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.

8. ### Gordon Gekko

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

9. ### stu

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

10. ### ktm

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

#10     Jul 2, 2003
ET IS FREE FOR TRADERS BECAUSE OF THE FINANCIAL SUPPORT FROM THESE SPONSORS: