excel formula help (identifying letters)

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

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

  2. the number of 'cs'? please clarify.
  3. 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.

  5. Sub Myassitches()


    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. WarEagle

    WarEagle Moderator

    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. thx, dudes... i'll get it to work somehow. if not, i'll be back. :)
  9. stu



    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.


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


  10. ktm


    Very nice use of the SUBSTITUTE function. I had not thought of that one. Great suggestion.
    #10     Jul 2, 2003