count number of times a particular letter appears in a cell

[fallowfz]

Is there a function which will return the number of times, for
example, the letter "E" appears in a cell containing a mix of text,
numbers, and other characters, e.g. (), :, etc?


Thanks!

 

[Dave Peterson]

=len(a1)-len(substitute(upper(a1),"E",""))
will count the number of lower or upper case E's in A1.

=len(a1)-len(substitute(a1,"E",""))
will count the number of upper case E's in A1.

(=substitute() is case sensitive)

 

[Chip Pearson]

You can do it with a formula:

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

This is case sensitive. For case insensitive, use

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E",""))

In code, use

Dim S As String
Dim N As Long
S = Range("A1").Text
N = Len(S) - Len(Replace(S, "e", vbNullString))
Debug.Print N

This is case sensitive. For case insensitive, use

Dim S As String
Dim N As Long
S = Range("A1").Text
N = Len(S) - Len(Replace(UCase(S), "E", vbNullString))
Debug.Print N

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mike H]

Hi,

Try this case sensitive idea

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

Mike

 

[fallowfz]

Hi,

Try this case sensitive idea

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

Mike






- Show quoted text -

WOW...thanks for the quick responses. All of them work great!