Re: Count letter string, e.g. h/EL/p

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The <> operator is better but Ron has us both beaten hands down if the OP
only want to count words not multiple occurrences within words.
--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@xxxxxxxxxxxxxx
Replace @mailinator.com with @tiscali.co.uk


"Tevuna" <Tevuna@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:21EF9468-03EC-4E68-952E-4B9C331C8F2B@xxxxxxxxxxxxxxxx
Or
=SUM(1*(LEN(A1:A3)<>LEN(SUBSTITUTE(A1:A3,"ee",""))))

"Sandy Mann" wrote:

One way:

With the string that you are looking for in D1 try:

=SUM(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),D1,"")))/LEN(D1)

Array entered with Ctrl + Shift + Enter

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@xxxxxxxxxxxxxx
Replace @mailinator.com with @tiscali.co.uk


"Lee" <Lee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D8BDDB65-7AC3-49C9-97F6-2425D2D204D2@xxxxxxxxxxxxxxxx
I want to count how many times a string of letters occurs in each row in
a
column. Column A has 1000 rows with 1 word in each row (1000 most
frequent
English words). e.g. I would like to count how many words contain the
string
of letters "ee" (sEEn).

I also want to count how frequent a string such as "A, any letter, E"
(mAdE)
occurs.

Thanks

Lee







.

Relevant Pages