Count the number of time a character appears in a string

[AndyD]

Hi can supply me with formula to count the number of time a character appears in a string

eg if cell A1 contains "Hello there my friend"...what is formula to count number of e's for example, thanks
Andy

 

[Mark W.]

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

 

[Andrew Poulsom]

Try:

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

where A1 contains your text.

Edit: Must press Refresh more often to avoid echoes. :wink:

 

[AndyD]

sorry i think i picked a really bad example of "e"....should've come out with what i actually wanted (silly me!)

i need to count number of dots in a string...this formula doesnt work for dots

 

[Mark W.]

Simple change "e" to "..." (or the ellipsis character).

 

[AC]

How about if you want to count the number of times a word is in a text string, like this

This is a test to count the number of times test is in this cell, this is for testing only
using =LEN(A1)-LEN(SUBSTITUTE(A1,"test","")) I get 12, should be 2, can this be done?

 

[Andrew Poulsom]

=(LEN(A1)-LEN(SUBSTITUTE(A1,"test","")))/LEN("test")

 

[IML]

using the same logic
=(LEN(A1)-LEN(SUBSTITUTE(A1," test ","")))/LEN(" test ")

note the spaces

 

[AC]

Thanks for the replys

andrew, yours stills counts testing

IML, that works for the text I gave, tried it with another test at the end and it did not find it, put another word after it and it worked then, so it want find the word if it is the last word in the string, can this be fixed??

 

[Aladin Akyurek]

Thanks for the replys

andrew, yours stills counts testing

IML, that works for the text I gave, tried it with another test at the end and it did not find it, put another word after it and it worked then, so it want find the word if it is the last word in the string, can this be fixed??

=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&A1&" "," test ","")))/LEN(" test ")