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!

NumberOfEs = Len(Text) - Len(Replace(Text, "E", ""))


CE



fallowfz wrote:
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)


fallowfz wrote:

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

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)


On Mon, 24 Nov 2008 10:51:43 -0800 (PST), fallowfz
wrote:

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!

Hi,

Try this case sensitive idea

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

Mike

"fallowfz" wrote:

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!

On Nov 24, 2:12*pm, Mike H wrote:
Hi,

Try this case sensitive idea

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

Mike



"fallowfz" wrote:
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!- Hide quoted text -

- Show quoted text -

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