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
<(E-Mail Removed)> 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 <Mi...@discussions.microsoft.com> 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!