count number of times a particular letter appears in a cell

  • Thread starter Thread starter fallowfz
  • Start date Start date
F

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!
 
=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)
 
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)
 
Back
Top