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

 

[Richie(UK)]

Thanks Andrew - that's my 'you learn something new every day' for today! :wink:

 

[Aladin Akyurek]

Hi Guys,

Sorry to butt in, but this is weird.

I entered "...test" (three full stops followed by the word test) and copied in the formula. It showed an answer of 0 (nil). I removed the three stops and it still showed 0 (as expected). Then when I re-entered the stops the result shows as 3 (Hooray!).

(Similarly, just doing Len(A1) with the same string gives an answer of 5!)

Aladin - your formula seems to have the same consequences. That is, 0 initially but then the correct count after editing.

I have the impression that the OP has not been clear about the real purpose for which a formula is needed. But keeping the issue just around counting, you have to read carefully what I'm proposing:

counting a char versus counting a word.

.is a char.

test is a word.

A word is entity that is delimited by usually a space in our world. This is of course what I mean by a word (count).

... is not a char nor a word if not surrounded by a delimiter.

If you had:

... Richie Aladin ... are discussing words.

then ... can be treated as a word because of a surrounding space. Then 2nd formula I proposed would apply with 2 as result.

 

[Andrew Poulsom]

... is not a char nor a word if not surrounded by a delimiter.

Actually it seems that Excel thinks it is a char (number 133).

 

[Aladin Akyurek]

... is not a char nor a word if not surrounded by a delimiter.

Actually it seems that Excel thinks it is a char (number 133).

:D

I picked out the right dot sequence, didn't I? Do you know how to escape the ellipsis interpretation?

PS. My "point" about char vs word count still stands!

 

[Andrew Poulsom]

If I type ...A in cell A1:

LEN(A1) returns 2
LEFT(A1) returns three dots
CODE(LEFT(A1)) returns 133.

If I edit the cell, delete the ... (one keypress) and precede the A with ...

LEN(A1) returns 4
LEFT(A1) returns one dot
CODE(LEFT(A1)) returns 46.

So editing the cell is the only way I know to escape the Ellipsis interpretation.

Incidentally the same thing happens if 3 dots are typed in the middle of the entry or at the end. And 4 dots is 2 characters, 5 dots is 3 characters, but 6 dots is only 2 characters.

 

[Aladin Akyurek]

If I type ...A in cell A1:

LEN(A1) returns 2
LEFT(A1) returns three dots
CODE(LEFT(A1)) returns 133.

If I edit the cell, delete the ... (one keypress) and precede the A with ...

LEN(A1) returns 4
LEFT(A1) returns one dot
CODE(LEFT(A1)) returns 46.

So editing the cell is the only way I know to escape the Ellipsis interpretation.

Incidentally the same thing happens if 3 dots are typed in the middle of the entry or at the end. And 4 dots is 2 characters, 5 dots is 3 characters, but 6 dots is only 2 characters.

It's probably better to substitute something else for "." before counting.

 

[Juan Pablo González]

FWIW, My Excel XP is the only one with the Autocorrect setting to replace "..." with the ellipsis character. Excel 97 or Excel 2000 don't have it "pre-included"

 

[Andrew Poulsom]

That's strange. My Excel 97 and Excel 2000 both have it in AutoCorrect. I'm sure I didn't put it there! :wink:

 

[ehstumpf]

Try:

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

where A1 contains your text.

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

OMG that's brilliant... I had been racking my brain coming up with all kinds of complex ways to accomplish this same thing and you do it in one line.