Monday, May 24, 2010

Excel 2003: Count the Number of Times a Letter Appears in a Cell

I had tracked down the solution to this problem once before, and then couldn’t remember it or find it when I needed it again, so here it is.  It’s borrowed from another source, but I don’t mind, as long as it meets the need.

The question is, how do you count the number of times a letter occurs within a cell, in Microsoft Excel 2003?  I was searching for this:

count occurrences "of a letter in a cell" "excel 2003"

when what I should have been searching for was this:

“Count the times a specific character appears in a cell”

but probably not this:

"excel 2003" "Count the number of times a character appears in a cell"

Anyway, the solution is to use either of these:

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

The former is simpler, and it works.