Count specific values in a cell

[jason500]

How would I count how many underscores ( _ ) there are in a cell. For example,
Cell contains:
word_word_word_word_word
Result would be 4.

Is there a formula for this?

Thanks

[jason500]

Got it.

Found a formula that counts words.
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

I replaced the space " " with my underscore "_"

[sandeep.warrier]

The +1 at the end would give you the number of words and not the number of _'s... If you are looking for the number of _'s remove the +1 from the end of the formula.

[daddylonglegs]

Are you trying to count underscores specifically or words given that every two words are separated by an undescore?

That formula works specifically for spaces and won't always count accurately for underscores (because TRIM removes excess spaces but not excess underscores)

If you just want to count underscores specifically you only need this

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

so if you really want to count words and you are sure there's only a single underscore between each word, then the word count would be

=LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))+(A1<>"")

or if it's possible you might have excess underscores (e.g. 2 between words or some at the start or end) then count the words with this

=LEN(TRIM(SUBSTITUTE(A1,"_"," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,"_"," "))," ",""))+(LEN(SUBSTITUTE(A1,"_",""))>0)